Run following simple script on SQL Server 2005 to retrieve all stored procedure in database.
SELECT *
FROM sys.procedures;
This will ONLY work with SQL Server 2005.
Reference : Pinal Dave (https://blog.sqlauthority.com)
SQL SERVER – 2005 – List All Stored Procedure in Database
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
66 Comments. Leave new
How can I find stored procedures that have never been executed. Not only in the time the log persists in memory? Any idea?
If is not posible I am trying to find out how to save the number of times the stored procedures of my database are executed. Any help please. It is driving me crazy!! Maybe there is a table where all this information is saved.
List Stored Procedures including plain script.
List Stored Procedures including plain script:
SELECT r.ROUTINE_NAME, r.Routine_Definition
FROM INFORMATION_SCHEMA.Routines r order by r.ROUTINE_NAME
thanks and it was very helpful and new to me other than sp_help
I need to copy a store procedures to txt file, by coding only
How to find DBName using ‘SPname’ in sql on which DB it exists.
SELECT * FROM SYSOBJECTS WHERE XTYPE IN (‘P’,’X’) AND category=0 ORDER BY NAME
HOW CAN I RESOLVE THIS
list the titles, prices and advances
of all the books with the
price greater than 20% of the advance
thanks
Hi,
I need to pull the all userdefined object info from one database.
User defined objects – Tables,index,constraints ,SP,Functions Triggers….
i want the output as below
DBName ObjectName ObjectType …..
Can anyone help me please
I have a phantom stored procedure that keeps running. How do i find and delete this phantom stored procedure?
What did you mean by phantom stored procedure?
Dear sir,
I have received one database from client i had made changes like, create,insert,update, delete, in table and stored procedures, could you please help me how to find the things which i had updated by using any query…
Hi Pinal,
Can you please tell me, how to get the list of user stored procedure that are not executed for long time or unused user stored procedure.
any one help me how can i find difference for two database like find stored procedure , table, function.. is there any tool or query ?
All stored numbers got lost deleted from phone memory