How to Identify Parameter information of a procedure in SQL Server

Problem statement:

Of late, one of my colleague was looking for a script to identify the procedures without any parameters.

Script:

The below script will identify the procedures without any parameter.


SELECT SCHEMA_NAME(A.SCHEMA_ID) AS SCHEMA_NAME
    ,A.NAME AS OBJECT_NAME
    ,A.TYPE_DESC,A.TYPE
FROM 
SYS.OBJECTS A
LEFT JOIN (SELECT O.OBJECT_ID FROM SYS.OBJECTS AS O 
INNER JOIN SYS.PARAMETERS AS P ON O.OBJECT_ID = P.OBJECT_ID AND O.TYPE='P') O ON A.OBJECT_ID= O.OBJECT_ID
WHERE O.OBJECT_ID IS NULL AND A.TYPE='P'
ORDER BY SCHEMA_NAME, OBJECT_NAME;

For any chance, if you are looking for identifying information about parameters for procedures, you can use the below script:



SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id and o.type='p'
/*************SEARCH SCENARIO*************/
--WHERE o.object_id = OBJECT_ID('schemaname.procedurename')
/*************SEARCH SCENARIO*************/
ORDER BY schema_name, object_name, p.parameter_id;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s