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)

See Also

Concepts

Creating Stored Procedures (Database Engine)

Creating CLR Stored Procedures

Executing Stored Procedures (Database Engine)

Recompiling Stored Procedures

Modifying Stored Procedures

Deleting Stored Procedures

Understanding SQL Dependencies