Have you ever wondered how some DBAs know about stored procedures you never heard of? Have you ever wanted to get a listing of stored procedures in SQL to see if there is one you might want to start using? In this article, we will explore how you can easily obtain a list of stored procedures in SQL Server.
Using System Tables
SQL Server 2005 stores a listing of all (documented, undocumented, and user-defined) stored procedures in the system tables. The simplest method to access this information is by using the sys.all_objects view. This view contains the identifying information of a stored procedure, such as its name, creation date, modification date, and source.
Here is an example of the basic T-SQL code to retrieve all stored procedures:
USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects WHERE [type] = 'P' ORDER BY [name];
GO
This code will return a list of all stored procedures in the specified database. You can modify the WHERE clause to filter the results based on your requirements.
Filtering by Object Type
If you want to filter the results by object type, you can adjust the WHERE clause accordingly. The following table shows the object types and their corresponding initials:
| Initial(s) | Type Of Object Returned |
|---|---|
| FN | SQL Scalar Function |
| IF | Inline Table Valued Function |
| P | SQL Stored Procedure |
| PC | CLR Stored Procedure |
| TF | SQL Table Valued Function |
| V | View |
| X | Extended Stored Procedure |
For example, if you only want to retrieve views, you can modify the code as follows:
USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects WHERE [type] = 'V' ORDER BY [name];
GO
Filtering by Origin
You can also filter the results based on the origin of the stored procedures. The “is_ms_shipped” field determines if the stored procedure was originally shipped by Microsoft. If the value of this field is 0, it means the stored procedure was not created by Microsoft. If the value is 1, it means the stored procedure was created by Microsoft.
To retrieve only user-defined stored procedures, you can modify the code as follows:
USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects WHERE [type] = 'P' AND [is_ms_shipped] = 0 ORDER BY [name];
GO
By using the above code, you can obtain a listing of all user-defined stored procedures in the specified database.
Conclusion
With a little bit of code and an understanding of where the information is stored, you can easily and quickly get your own listing of stored procedures in SQL Server. This eliminates the need to search the internet for stored procedures and allows you to discover new functionalities that can make your job easier. However, it is important to note that undocumented stored procedures, extended stored procedures, and other undocumented objects are not supported by Microsoft and should be used with caution.