Home » Get the definitions of Stored procedures, User Defined functions and Triggers in SQL

Get the definitions of Stored procedures, User Defined functions and Triggers in SQL

How to get the full text descriptions / definitions of any stored procedures , UDF  or triggers in SQL ?




There are many ways to get the definition of any Stored Procedure, UDF and triggers in SQL , few of them are listed below

Sp_helptext

DECLARE @tbl TABLE (txt NVARCHAR(MAX)) ;
DECLARE @Desc NVARCHAR(MAX) = '' ;

INSERT @tbl EXEC sp_helptext 'fn_getorderDetails' ;
SELECT @Desc = @Desc + txt FROM @tbl ;

PRINT @Desc ;

sys.sql_modules

SELECT O.name, M.definition, O.type_desc, O.type
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id=O.object_id
WHERE O.type IN ('IF','TF','FN' ,'TR','P')

Here type specifies the type of objects we are looking for

‘FN’ – Scalar function
‘IF’- Inline table valued Function
‘TF’ -Table valued Function
‘TR’- Trigger
‘P’ – Stored procedures

 

Get the Definitions of all SPs , UDF  and triggers based on specific text search

SELECT O.name, M.definition, O.type_desc, O.type
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id=O.object_id
WHERE O.type IN ('IF','TF','FN' ,'TR','P')
and definition like '%Orders%'

It returns all the Sps, UDF or triggers ,using any variables , tables or text which contains ‘Orders’.

 




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.