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? Have you especially ever wondered how DBAs learn about the existence of the "UNDOCUMENTED" stored procedures? Have you ever wanted to know if you were missing a function? How about a missing a possible view you could be using to make your job easier?

Well, you could spend your time browsing the Internet trying to find a listing of what exists in SQL, or maybe just to find a stored procedure you don't already know about. Who knows, maybe after an hour or two you might find what you want...or maybe you can get really lucky and find someone that has taken the time to list all of the SP, Functions and Views they know about and/or use.

Actually, you can get your own list of these in less than a minute! This list even includes all of the undocumented SPs that exist in SQL Server. What's even best is that this is all easily ready at your finger tips for being discovered! I’ll cover Stored Procedures, but using the table below to determine the object type you wish to find, you can easily adapt this to list out functions and/or views…or even ALL of them if you are daring to read that large of a list!

SQL Server 2005 actually stores a listing of ALL (documented, undocumented, and even user-defined) stored procedures! Yes, SQL Server 2005 even keeps a list of all SPs that you (or anyone else) has added to the database. It's all contained in the system tables. The simplest method is to use a system view; specifically the sys.all_objects view. Now, you could use the sys.procedures view...but my testing contained results that had filtered out some stored procedures. You could also use a Stored Procedure called sp_stored_procedures; again, this limits the stored procedures that are returned. I don't know about you, but I wanted to know ALL stored procedures that SQL Server 2005 contains. Maybe there is a good reason that some of these SPs are not included in sys.Procedures or sp_Stored_Procedures; I don’t know what it is, if there is such a reason.

Where Stored Procedures are and what information you can get

Before I get to the TSQL code I want to clarify what is stored and where it is stored. First, the what is stored; SQL Server 2005 only stores the identifying information of a SP. Information such as the name of the SP, when it was created, when it was modified, and if it came from MS or not. Don't expect to find the documentation and examples; you still need to use Books Online (BOL) for the supported SPs, and for those "UNDOCUMENTED" SPs...Well, you’re still on your own there, but at least now you know what other undocumented SPs exist. They are, after all, undocumented and MS clearly claims they are not in place for the purpose of being used; they were only there originally for diagnostic reasons during development of SQL Server itself.

Second, the where it is stored; I already mentioned the SP information is stored in the system tables. I want to be a little clearer on this; the SP is stored in the system table of the database the SP was created for. So, how can so many SPs be in all of the databases you have created? That's because the MS provided SPs are created (and stored originally) in the 'master' database. Which, of course, the 'master' database is the "template" database used whenever you create a new database (thus, the SPs within the 'master' database are automatically created for the databases you create). One last note, if you want to create an SP and have it distributed (automatically included) with all databases you create then you will want to create the SP for the 'master' database. Also, this only gets used with newly created databases from the point after you created the SP...So, if you already have existing databases you will still have to create the SP for each one of them.

Listing ALL Stored Procedures

Now, on to the TSQL coding! Below is the most basic coding to find out ALL of the Stored Procedures that exist within SQL Server 2005.

USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];
GO

Your results will look similar to the following if you have used all default settings when installing and using SQL Server 2005 (NOTE: This is only a partial listing and could vary depending on settings and modifications to the database made):

This coding is based on filter by the object "type". Use the following table to determine the type of object you want returned, and then adjust the WHERE clause to only return those type(s) you wish to be returned. This listing only lists views, functions, and procedures. There are other types which you can also filter, be sure to review BOL for further information on the types.

User-Defined Stored Procedures

Now, one other field that is worthwhile to filter by is the "is_ms_shipped" field. This, obviously, determines if the SP was originally shipped by Microsoft. If the value of the field is 0 then it was NOT shipped by MS; if the value is 1 then it WAS shipped by MS. Simple enough. If you want to use it in conjunction with the above TSQL statement you will put in "AND [is_ms_shipped] = 0" to find SPs that were NOT created by MS, and most likely created by yourself or someone else with access to your database. If you ONLY want MS provided SPs then change the value from 0 to 1.

Here is a TSQL coding example of how to obtain only User-Defined SPs (using the previously mentioned "is_ms_shipped" field to obtain the listing). Note: This listing will definitely vary depending on what SPs you have installed. If you received no results then this indicates the database you are running your query on does not contain any SPs that were not created by MS...Try another database that you know you have created an SP for, or create an SP for your database and re-run the query.

USE [your_database_name_here];
GO
SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [is_ms_shipped] = 0
ORDER BY [name];
GO

Then you would get a result pane (below is an example of my results just to show how no MS shipped SPs were included, your results will be specific to any SPs you or anyone else using your database has created):

Conclusion

Now you can see that with a little bit of code and understanding of where information is stored you can easily and quickly get your own listing of stored procedures. You will never have to search another website again to find a Stored Procedure. You may even learn of a stored procedure that has existed all this time to do some sort of operation that you have been trying to get done but have had complications in scripting it out. Now the only tricky part is learning what all of these Stored Procedures are used for...and if you’re at all lucky learning what the UNDOCUMENTED SPs are used for.

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.