SQL Server – List-out Procedures, Functions, Triggers

In order to know which procedures, functions, and triggers exist in the database, the following queries can help you to find this out.:

The below set of statements can be used to retrieve stored procedures and the number of parameters in it:

Use [Test1]
Go

SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME)
FROM INFORMATION_SCHEMA.PARAMETERS
GROUP BY SPECIFIC_NAME

The below statements are an alternative to the statements above and can be used for the same purpose:

Use [Test1]
Go

SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME),
CASE WHEN xtype = ‘P’ THEN ‘Stored Procedure’
WHEN xtype IN (‘FN’, ‘IF’, ‘TF’) THEN ‘Function’
END AS ProcType
FROM INFORMATION_SCHEMA.PARAMETERS a
INNER JOIN sysobjects b ON b.name = a.SPECIFIC_NAME
GROUP BY SPECIFIC_NAME, xtype

To retrieve data about a specific procedure, either the following statement:

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME=3D’MentionProcName’

Or the below statement can be used to list out the procedure details:

EXEC sp_stored_procedures

To know the parameters set for the desired procedures, you can use the below SELECT statement:

SELECT a.name, a.type_desc, b.name, parameter_id FROM sys.procedures a
INNER JOIN sys.parameters b ON a.object_ID = b.object_ID
ORDER BY a.name, parameter_id

Note that Parameter_ID in the above query refers to a serial order of the parameters that belong to the procedures.

The following statements can help you to retrieve the stored procedures or functions along with the number of lines in the code:

SELECT so.name AS ProcedureName,
CONVERT(INT, (LEN(sc.text) – LEN(REPLACE(sc.text, char(10), ”))))-1 as CodeLines,
CASE WHEN so.xtype = ‘P’ THEN ‘Stored Procedure’
WHEN so.xtype IN (‘FN’, ‘IF’, ‘TF’) THEN ‘Function’
END AS ObjectType
FROM sysobjects so
INNER JOIN syscomments sc ON sc.id = so.id
WHERE so.xtype IN (‘P’, ‘FN’, ‘IF’, ‘TF’) AND so.category = 0

To the above statement, you can include the following clause to avoid system procedures / functions.
AND so.name NOT IN
(‘fn_diagramobjects’,
‘sp_alterdiagram’,
‘sp_creatediagram’,
‘sp_dropdiagram’,
‘sp_helpdiagramdefinition’,
‘sp_helpdiagrams’,
‘sp_renamediagram’,
‘sp_upgraddiagrams’,
‘sysdiagrams’)

And the following query can help you to list the triggers that are created in the database:

SELECT OBJECT_NAME(ID), OBJECT_NAME(deltrig),
OBJECT_NAME(instrig), OBJECT_NAME(updtrig)
FROM sysobjects
WHERE xtype =’U’ AND (deltrig > 0 OR instrig > 0 OR updtrig > 0)