Retrieving Stored Procedure Names in SQL Server

In SQL Server, stored procedures are reusable blocks of code that perform a specific task. They can be used to simplify complex operations, improve performance, and enhance data security. To manage and maintain these stored procedures effectively, it’s essential to be able to retrieve their names.

This tutorial will cover the different methods for listing all stored procedure names in a SQL Server database. We’ll explore various system views and tables that provide this information, along with examples of how to use them.

Using INFORMATION_SCHEMA.ROUTINES

The INFORMATION_SCHEMA.ROUTINES view is a standard way to retrieve metadata about stored procedures, functions, and other database objects. To list all stored procedure names, you can use the following query:

SELECT * 
FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

This query returns all columns (*) from the INFORMATION_SCHEMA.ROUTINES view for the specified database, filtering only rows where ROUTINE_TYPE is 'PROCEDURE'. This method excludes system stored procedures, which are typically stored in the master database.

If you need to retrieve stored procedure names from the master database, you can use a similar query with an additional filter to exclude most system stored procedures:

SELECT * 
FROM [master].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' 
  AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')

Using sys.procedures

Another way to retrieve stored procedure names is by querying the sys.procedures system view:

SELECT * FROM sys.procedures

This query returns all columns (*) from the sys.procedures view, which contains information about all stored procedures in the current database.

Using sysobjects

The sysobjects system table is another option for retrieving stored procedure names. You can use the following query:

SELECT name, type
FROM dbo.sysobjects
WHERE (type = 'P')

This query returns only the name and type columns from the sysobjects table, filtering rows where type is 'P', which indicates a stored procedure.

Retrieving Additional Database Objects

If you need to retrieve names of other database objects, such as functions or tables, you can modify the previous queries accordingly. For example, to retrieve both stored procedures and functions, you can use:

SELECT name, type
FROM dbo.sysobjects
WHERE type IN (
    'P', -- stored procedures
    'FN', -- scalar functions 
    'IF', -- inline table-valued functions
    'TF' -- table-valued functions
)
ORDER BY type, name

This query returns the name and type columns for both stored procedures ('P') and various types of functions ('FN', 'IF', and 'TF'), ordered by type and then by name.

In conclusion, SQL Server provides several methods for retrieving stored procedure names, each with its own advantages and use cases. By understanding how to use these system views and tables effectively, you can manage your database objects more efficiently and improve your overall database administration skills.