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’.

 

 2,778 total views,  2 views today