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

New: 5 December 2005

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 SQL Server 2005 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 AdventureWorks;
   GO
   SELECT definition
   FROM sys.sql_modules
   WHERE object_id = (OBJECT_ID(N'AdventureWorks.dbo.uspLogError'));
   

To view the definition of a stored procedure by using OBJECT_DEFINITION

1. In Object Explorer, connect to an instance of the SQL Server 2005 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 AdventureWorks;
   GO
   SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks.dbo.uspLogError')); 
   

To view the definition of a stored procedure by using sp_helptext

1. In Object Explorer, connect to an instance of the SQL Server 2005 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 AdventureWorks;
   GO
   EXEC sp_helptext N'AdventureWorks.dbo.uspLogError';
   

See Also

Concepts

Viewing Stored Procedures
Viewing Object Definitions

Other Resources

OBJECT_DEFINITION (Transact-SQL)
sys.sql_modules (Transact-SQL)
sp_helptext (Transact-SQL)
OBJECT_ID (Transact-SQL)
Stored Procedure How-to Topics

Help and Information

Getting SQL Server 2005 Assistance