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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Subscribe to get the latest posts sent to your email.
SQL Server slow? Let's fix it together in about four hours.
In my Comprehensive Database Performance Health Check, you share your screen, I guide you, and we fix your biggest performance troublemakers. No passwords. No remote access.
You keep every script, and the know-how to fix it yourself next time.
Want your whole team to learn it?
My Practical Workshop has no PowerPoint, just live demos. My AI Enablement Workshop shows your team where AI saves hours, and where it quietly costs them.
Over 400 customers have worked with me this way, and not one shared a password. See all consulting options.
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