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;