Microsoft SQL Server Retrieve information about the database Retrieve a List of all Stored Procedures
Example
The following queries will return a list of all Stored Procedures in the database, with basic information about each Stored Procedure:
SQL Server 2005
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
The ROUTINE_NAME, ROUTINE_SCHEMA and ROUTINE_DEFINITION columns are generally the most useful.
SQL Server 2005
SELECT *
FROM sys.objects
WHERE type = 'P'
SQL Server 2005
SELECT *
FROM sys.procedures
Note that this version has an advantage over selecting from sys.objects since it includes the additional columns is_auto_executed, is_execution_replicated, is_repl_serializable, and skips_repl_constraints.
SQL Server 2005
SELECT *
FROM sysobjects
WHERE type = 'P'
Note that the output contains many columns that will never relate to a stored procedure.
The next set of queries will return all Stored Procedures in the database that include the string 'SearchTerm':
SQL Server 2005
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id=o.id
WHERE o.xtype = 'P'
AND c.TEXT LIKE '%SearchTerm%'
SQL Server 2005
SELECT p.name
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
ON m.object_id = p.object_id
WHERE definition LIKE '%SearchTerm%'
