Posts Tagged ‘sys.procedures’

Using Catalog Views – sys.procedures

10.06.2011 No comments

sys.procedures contains a row for each stored procedure in the database. Below are some useful columns returned by sys.procedures

1. Name – name of the stored procedure,

2. Object_ID – object id of the procedure

3. Type – type of procedure, it can be P = SQL Stored Procedure, X = Extended stored procedure, RF = Replication-filter-procedure, PC = Assembly (CLR) stored-procedure

4. Type_Desc – textual description of type

5. Create_Date – creation date

6. Modify_Date – last modification date


Rows returned by sys.procedures is limited by user permissions. If a user does not have permission on a stored procedure, it will not be returned by this view. This is also true for all catalog views.

— © 2011 – Vishal (


USE AdventureWorks2008R2


SELECT      Name, Object_ID, Type, Type_Desc,

            Create_Date, Modify_Date

FROM        sys.procedures

Since I am using 'sa' to execute this all stored procedures will be returned.


Hope This Helps!