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