Friday, October 23, 2009

Function and Procedure Parameters

My task was to find all table-valued functions that had no parameters. I used the DMV sys.parameters.
SELECT AS FunctionName
FROM sys.objects                AS o
LEFT OUTER JOIN sys.parameters  AS 
o.[object_id] p.[object_id]
WHERE o.[type] 'IF'
AND p.[object_id] IS NULL
This could be easily switched to stored procedures.
SELECT AS ProcedureName
FROM sys.procedures             AS s
LEFT OUTER JOIN sys.parameters  AS 
s.[object_id] p.[object_id]
WHERE p.[object_id] IS NULL
To find all functions that have a particular parameter, you could do this:
SELECT          AS FunctionName,
p.parameter_id  AS ParameterOrder
FROM sys.objects    AS o
JOIN sys.parameters AS p
ON o.[object_id] p.[object_id]
WHERE o.[type] 'IF'
AND '@PersonID'

No comments:

Post a Comment