List all System Stored Procedures in SQL Server

In order to list all the System Stored Procedures in SQL Server 2005/2008, you can query the sys.all_objects catalog view, that displays all schema-scoped user-defined objects as well as system objects.

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='P'

OUTPUT (Partial)

image

Similarly to find CLR Stored Procedures, use type=’PC’

SELECT NAME FROM SYS.ALL_OBJECTS WHERE type='PC'

OUTPUT

image

Note: You can add ‘is_ms_shipped = 1’ to the query if you intend listing only those objects created by an internal SQL Server component.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

No comments: