Viewing Stored Procedures

Several system stored procedures and catalog views provide information about stored procedures. Using these, you can:

• See the definition of the stored procedure. That is, the Transact-SQL statements used to create a stored procedure. This can be useful if you do not have the Transact-SQL script files used to create the stored procedure.

• Get information about a stored procedure such as its schema, when it was created, and its parameters.

• List the objects used by the specified stored procedure, and the procedures that use the specified stored procedure. This information can be used to identify the procedures affected by the changing or removal of an object in the database.

To view the definition of a stored procedure

• sys.sql_modules (Transact-SQL)

• OBJECT_DEFINITION (Transact-SQL)

• sp_helptext (Transact-SQL)

To view information about a stored procedure

• sys.objects (Transact-SQL)

• sys.procedures (Transact-SQL)

• sys.parameters (Transact-SQL)

• sys.numbered_procedures (Transact-SQL)

• sys.numbered_procedure_parameters (Transact-SQL)

• sp_help (Transact-SQL)

To view the dependencies of a stored procedure

• sys.sql_expression_dependencies (Transact-SQL)

• sys.dm_sql_referenced_entities (Transact-SQL)

• sys.dm_sql_referencing_entities (Transact-SQL)

To view information about an extended stored procedure

• sp_helpextendedproc (Transact-SQL)