Microsoft SQL Server Retrieve information about the database Retrieve a List of all Stored Procedures

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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%'


Got any Microsoft SQL Server Question?