sqlserver列出所有的存储过程

This article presents two ways to return a list of stored procedures in a SQL Server database.

Option 1 – The ROUTINES Information Schema View

You can use the ROUTINES information schema view to get a list of all user-defined stored procedures in a database.

USE Music;
SELECT 
  ROUTINE_SCHEMA,
  ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

Result:

+------------------+----------------------+
| ROUTINE_SCHEMA   | ROUTINE_NAME         |
|------------------+----------------------|
| dbo              | spAlbumsFromArtist   |
| dbo              | uspGetAlbumsByArtist |
+------------------+----------------------+

Return The Procedure’s Definition

The INFORMATION_SCHEMA.ROUTINES view also has a ROUTINE_DEFINITION column, so you can easily return each stored procedure’s definition if required.

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

Option 2 – The sys.objects System Catalog View

Another way to return a list of stored procedures is to query the sys.objects system catalog view.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name
FROM sys.objects
WHERE type = 'P';

Result:

+----------+----------------------+
| Schema   | name                 |
|----------+----------------------|
| dbo      | spAlbumsFromArtist   |
| dbo      | uspGetAlbumsByArtist |
+----------+----------------------+

The type P is presumable for “Procedure”.

Another way to do this is filter by the type_desc column:

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  name
FROM sys.objects
WHERE type_desc = 'SQL_STORED_PROCEDURE';

Return The Procedure’s Definition

The sys.objects view doesn’t include a column for the object’s definition. If you want to return each stored procedure’s definition, you can join it with the sys.sql_modules system view.

Example:

SELECT definition
FROM sys.objects o
INNER JOIN sys.sql_modules m 
ON o.object_id = m.object_id
WHERE type = 'P';

Option 3 – The sys.procedures Catalog View

The sys.procedures catalog stored procedure contains a row for each object that is a procedure of some kind, with sys.objects.type = P, X, RF, and PC.

Executing the following code will return all stored procedures that the user either owns or on which the user has been granted some permission.

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema],
  Name
FROM sys.procedures;

Result:

+----------+----------------------+
| Schema   | Name                 |
|----------+----------------------|
| dbo      | spAlbumsFromArtist   |
| dbo      | uspGetAlbumsByArtist |
+----------+----------------------+

This view inherits the type column from sys.objects so you can filter the results by procedure type if you wish.

SELECT 
  SCHEMA_NAME(schema_id),
  name
FROM sys.procedures
WHERE type = 'P';

In my case, I get the same result because both of my procedures are of type “P”.

In case you’re wondering, here’s what each type means.

P

SQL Stored Procedure

X

Extended stored procedure

RF

Replication-filter-procedure

PC

Assembly (CLR) stored-procedure

Return The Procedure’s Definition

The sys.procedures view doesn’t include a column for the object’s definition. As with the previous method, if you want to return each stored procedure’s definition, you can join it with the sys.sql_modules system view.

Example:

SELECT definition
FROM sys.procedures p
INNER JOIN sys.sql_modules m 
ON p.object_id = m.object_id;