In SQL Server, you can use the Transact-SQL ROUTINES system information schema view to return a list of stored procedures and functions in the current database.

More specifically, it returns a list of all stored procedures and functions that can be accessed by the current user in the current database.

You can also use ROUTINES simply to return information about a specific procedure or function if required.

To use this view, use the fully qualified name of INFORMATION_SCHEMA.ROUTINES.

Example 1 – Return All Routines

Here’s a quick example that returns all stored procedures and functions in the current database (that the current user has access to).

USE Music;
SELECT 
  ROUTINE_CATALOG,
  ROUTINE_SCHEMA,
  ROUTINE_NAME,
  ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES;

Result:

Changed database context to 'Music'.
+-------------------+------------------+----------------------+----------------+
| ROUTINE_CATALOG   | ROUTINE_SCHEMA   | ROUTINE_NAME         | ROUTINE_TYPE   |
|-------------------+------------------+----------------------+----------------|
| Music             | dbo              | ISOweek              | FUNCTION       |
| Music             | dbo              | ufn_AlbumsByGenre    | FUNCTION       |
| Music             | dbo              | spAlbumsFromArtist   | PROCEDURE      |
| Music             | dbo              | uspGetAlbumsByArtist | PROCEDURE      |
+-------------------+------------------+----------------------+----------------+
(4 rows affected)

These results show me that there are two functions and two procedures that I have access to in the “Music” database.

The view returns quite a large number of columns, so in this example I narrowed the columns down to just four.

Example 2 – Find a Specific Routine

You can also narrow the results to a given procedure or function if you prefer.

In this example I include all columns. I use vertical output so that you aren’t forced to scroll horizontally.

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'uspGetAlbumsByArtist';

Result (using vertical output):

-[ RECORD 1 ]-------------------------
SPECIFIC_CATALOG         | Music
SPECIFIC_SCHEMA          | dbo
SPECIFIC_NAME            | uspGetAlbumsByArtist
ROUTINE_CATALOG          | Music
ROUTINE_SCHEMA           | dbo
ROUTINE_NAME             | uspGetAlbumsByArtist
ROUTINE_TYPE             | PROCEDURE
MODULE_CATALOG           | NULL
MODULE_SCHEMA            | NULL
MODULE_NAME              | NULL
UDT_CATALOG              | NULL
UDT_SCHEMA               | NULL
UDT_NAME                 | NULL
DATA_TYPE                | NULL
CHARACTER_MAXIMUM_LENGTH | NULL
CHARACTER_OCTET_LENGTH   | NULL
COLLATION_CATALOG        | NULL
COLLATION_SCHEMA         | NULL
COLLATION_NAME           | NULL
CHARACTER_SET_CATALOG    | NULL
CHARACTER_SET_SCHEMA     | NULL
CHARACTER_SET_NAME       | NULL
NUMERIC_PRECISION        | NULL
NUMERIC_PRECISION_RADIX  | NULL
NUMERIC_SCALE            | NULL
DATETIME_PRECISION       | NULL
INTERVAL_TYPE            | NULL
INTERVAL_PRECISION       | NULL
TYPE_UDT_CATALOG         | NULL
TYPE_UDT_SCHEMA          | NULL
TYPE_UDT_NAME            | NULL
SCOPE_CATALOG            | NULL
SCOPE_SCHEMA             | NULL
SCOPE_NAME               | NULL
MAXIMUM_CARDINALITY      | NULL
DTD_IDENTIFIER           | NULL
ROUTINE_BODY             | SQL
ROUTINE_DEFINITION       | CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
SELECT AlbumName
FROM [fdec67e6a28d].[Music].[dbo].[Albums]
WHERE ArtistId = @ArtistId
EXTERNAL_NAME            | NULL
EXTERNAL_LANGUAGE        | NULL
PARAMETER_STYLE          | NULL
IS_DETERMINISTIC         | NO
SQL_DATA_ACCESS          | MODIFIES
IS_NULL_CALL             | NULL
SQL_PATH                 | NULL
SCHEMA_LEVEL_ROUTINE     | YES
MAX_DYNAMIC_RESULT_SETS  | -1
IS_USER_DEFINED_CAST     | NO
IS_IMPLICITLY_INVOCABLE  | NO
CREATED                  | 2019-10-26 01:11:30.677
LAST_ALTERED             | 2019-10-26 03:50:42.527
(1 row affected)

You’ll notice that many of the columns have null values. The Microsoft documentation for this view states that many of these columns always return NULL. It also states that some of them are reserved for future use.

Example 3 – Return Procedures Only

You can use a WHERE clause to filter the results to just stored procedures or just functions.

This example returns just stored procedures.

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

Result:

+-------------------+------------------+----------------------+----------------+
| ROUTINE_CATALOG   | ROUTINE_SCHEMA   | ROUTINE_NAME         | ROUTINE_TYPE   |
|-------------------+------------------+----------------------+----------------|
| Music             | dbo              | spAlbumsFromArtist   | PROCEDURE      |
| Music             | dbo              | uspGetAlbumsByArtist | PROCEDURE      |
+-------------------+------------------+----------------------+----------------+
(2 rows affected)

Example 4 – Return Functions Only

This example returns just functions.

SELECT 
  ROUTINE_CATALOG,
  ROUTINE_SCHEMA,
  ROUTINE_NAME,
  ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

Result:

+-------------------+------------------+-------------------+----------------+
| ROUTINE_CATALOG   | ROUTINE_SCHEMA   | ROUTINE_NAME      | ROUTINE_TYPE   |
|-------------------+------------------+-------------------+----------------|
| Music             | dbo              | ISOweek           | FUNCTION       |
| Music             | dbo              | ufn_AlbumsByGenre | FUNCTION       |
+-------------------+------------------+-------------------+----------------+
(2 rows affected)