This article presents 4 ways of using T-SQL to get the definition of a stored procedure in SQL Server.

The definition is the actual T-SQL statement used to create the stored procedure.

Three of the methods here are exactly the same as the ones used for returning the definition of a view (except here, they’re being used on stored procedures instead of views).

Example 1 – The sys.sql_modules System Catalog View

The sys.sql_modules system catalog view returns a row for each object that is an SQL language-defined module in SQL Server.

In other words, you can use this view to return information about objects of various types, including functions, views, and of course, stored procedures.

One of the columns returned with this view is called definition. As the name implies, this returns the definition of the object.

SELECT definition
FROM sys.sql_modules
WHERE object_id = object_id('uspGetAlbumsByArtist');

Result:

+--------------+
| definition   |
|--------------|
| CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
SELECT AlbumName
FROM [dbo].[Albums]
WHERE ArtistId = @ArtistId
              |
+--------------+

I used a command line interface (CLI) with this example, so the result is nicely formatted.

If you use a GUI (such as SSMS or Azure Data Studio) to return the results in a grid, the definition will likely be returned in one long line in a single cell. In such cases you’ll need to do some extra work if you want it displayed in a more readable format. Alternatively, you could use the sp_helptext method below.

Example 2 – The sp_helptext System Stored Procedure

Another method of returning a stored procedure’s definition is to use the sp_helptext system stored procedure. As well as being able to return the definition for (unencrypted) stored procedures, it can also return the definition of a user-defined rule, default, view, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, or system object such as a system stored procedure.

This stored procedure displays the definition across multiple rows. Each row contains 255 characters of the T-SQL definition.

Example:

EXEC sp_helptext 'uspGetAlbumsByArtist';

Here’s the result I get when using a GUI (Azure Data Studio):

And here’s what I get using my command line interface:

+--------+
| Text   |
|--------|
| CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
        |
| SELECT AlbumName
        |
| FROM [dbo].[Albums]
        |
| WHERE ArtistId = @ArtistId
        |
+--------+

Example 3 – The OBJECT_DEFINITION() Function

Another way to return the definition of a stored procedure is to use the OBJECT_DEFINITION() function. As with the previous methods, this method can also return the definition of other object types.

Here’s an example of using this function:

SELECT OBJECT_DEFINITION(
        OBJECT_ID('uspGetAlbumsByArtist')
    ) AS [Definition];

Result:

+--------------+
| Definition   |
|--------------|
| CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
SELECT AlbumName
FROM [dbo].[Albums]
WHERE ArtistId = @ArtistId
              |
+--------------+

Example 4 – The ROUTINES System Information Schema View

The ROUTINES system information schema view can also return the definition of stored procedures (and functions too).

This view returns many columns, one of which is the object’s definition. Therefore we can name that column to return just the definition:

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

Result:

+----------------------+
| ROUTINE_DEFINITION   |
|----------------------|
| CREATE PROCEDURE [dbo].[uspGetAlbumsByArtist] @ArtistId int AS
SELECT AlbumName
FROM [dbo].[Albums]
WHERE ArtistId = @ArtistId
                      |
+----------------------+

Note that the ROUTINE_DEFINITION column in this system view has a maximum length of nvarchar(4000). For definitions larger than this, you can use the OBJECT_DEFINITION() function or the sys.sql_modules view in the previous examples. Both of those use nvarchar(max) for the definition, so they don’t have the character limitation of the ROUTINE_DEFINITION column (which as mentioned, is nvarchar(4000)).