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.
