View the Stored Procedure’s Definition

We can view the stored procedure’s Definition in two way

·         SQL Server Management Studio GUI

·         Transact-SQL

SQL Server Management Studio GUI

Go to DatabaseàDatabase_nameàProgrammability à Stored Proceduresà SP nameàRight click and select àScript Stored procedure as/Modify à create toàNew Query Editor Window

   

A query window will be open as below.

 

With the help of SQL script we can find the definition.

·         sp_helptext

·         Information_Schema.Routines

·         Sys.Sql_Modules

SP_helptext

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

Syntax

sp_helptext 'sp_name_with_schema'

 sp_helptext 'dbo.uspGetBillOfMaterials'

Default schema is dbo.

See the example

 

Information_Schema.Routines

INFORMATION_SCHEMA.ROUTINES is a system information schema view. This system view can be used to fetch all the details about the stored procedure like name, definition / source code, database schema, created and modified date, etc...

SELECT     ROUTINE_NAME,     ROUTINE_DEFINITION ,     ROUTINE_SCHEMA FROM     INFORMATION_SCHEMA.ROUTINES WHERE     ROUTINE_TYPE='PROCEDURE' AND     ROUTINE_NAME LIKE '%uspGetBillOfMaterials%'

       

In the Routine Definition column, we will get the definition of the SP.

Sys.Sql_Modules

sys.sql_modules is a system object catalog view. This system view can be used to fetch the user-defined programmability object’s definition/source code.sys.sql_modules is a system object catalog view. This system view can be used to fetch the user-defined programmability object’s definition / source code.

Select Object_Name(Object_ID) as SP_Name, definition from Sys.Sql_Modules  where   Object_Name(Object_ID)  LIKE '%uspGetBillOfMaterials%'

   

Definition column we will get the definition of the SP.