Share via

  • Article

How to: View the Definition of a Stored Procedure (SQL Server Management Studio)

Several system stored procedures, system functions, and catalog views provide information about stored procedures. By using these system stored procedures, you can see the definition of a stored procedure: That is, the Transact-SQL statements that are used to create a stored procedure. This can be useful if you do not have the Transact-SQL script file used to create the stored procedure.

To view the definition of a stored procedure by using sys.sql_modules

  1. In Object Explorer, connect to an instance of the Database Engine, and then expand that instance.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2008R2;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'AdventureWorks2008R2.dbo.uspLogError'));

To view the definition of a stored procedure by using OBJECT_DEFINITION

  1. In Object Explorer, connect to an instance of the Database Engine, and then expand that instance.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2008R2;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2008R2.dbo.uspLogError'));

To view the definition of a stored procedure by using sp_helptext

  1. In Object Explorer, connect to an instance of the Database Engine, and then expand that instance.

  2. On the toolbar, click New Query.

  3. In the query window, enter the following statements. Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2008R2;
GO
EXEC sp_helptext N'AdventureWorks2008R2.dbo.uspLogError';