General discussion of SQL Topics; aimed for the novice-intermediate level Microsoft SQL Server User. Currently focuses on using SQL Server 2005.

Wednesday, June 18, 2008

Listing User-Defined Stored Procedures in SQL Server 2005

You can easily obtain a listing of the User-Defined Stored Procedures that are in any of your databases. In fact, Microsoft had made this information very easily accessible. You only need to access a System Table view called "Sys.Procedures". This information may be easily accessed using a small TSQL code, such as the following...

USE [your_database_name_here];
GO
SELECT * FROM sys.procedures
ORDER BY [name];
GO

Your results will vary based on what Stored Procedures you or anyone else with access to your database have created. The following is a sample of the results I have obtained when running this code on my 'model' database.

User-Defined Stored Procedures

The most important thing to keep in mind with this TSQL code is that each database may contain different User-Defined SPs.

Do you want to get a listing of EVERY single Stored Procedure in your database(s)? If so, then be sure to check out SQLServerCentral.com for one of my upcoming articles! I'll post the direct link to the article as soon as it becomes available!

You'll want to check this article out when it is published...you'll NEVER have to search the Internet again to find out what SPs are in SQL Server 2005!! UPDATE: You can read this article on SQLServerCentral.com (http://www.sqlservercentral.com/articles/T-SQL/63471/). Please leave feedback if you have a few moments.

TIP:

Do you want all of your databases you create (from here on...not previously created databases) to use a specified stored procedure or set of stored procedures? If so, then create the desired Stored Procedure(s) in your 'model' database. Now any new databases created will get the SP(s) you created in the 'model' database because your new databases are based on the 'model' database!

 

-----------------------

Legal Disclaimer/Warning
Please remember that when using an SP you are not sure the outcome or effect it has should be done on test systems; never use anything that has not been thoroughly tested in a production environment. I am not encouraging you to use any type of Stored Procedures (documented or undocumented); I am only informing you of the method that can be used to obtain a listing of all procedures that are found within SQL Server 2005. Microsoft strongly states that undocumented Stored Procedures, Extended Stored Procedures, functions, views, tables, columns, properties, or metadata are NOT to be used and do not have any associated support; and Microsoft Customer Support Services will not support any databases or applications that leverage or use any undocumented entry points. Please refer to: http://msdn.microsoft.com/en-us/library/ms166021.aspx for Microsoft's legal disclaimer and further information in Microsoft's support for use of stored procedures.