Problem
Parameters are widely used in user defined functions (UDF) and stored procedures (SP) in SQL Server. A user defined function in SQL Server can have up to 1,024 input parameters while a stored procedure can have 2,100 input parameters. I need to analyze the all of the parameters for my user defined functions and stored procedures in one of my databases. Specifically I want to know the parameters and the associated data types. So how can I access the user defined functions and stored procedures in one of my databases along with all their parameters to meet my needs?
Solution
Let’s check out the script below. In this example, I am using the AdventureWorks sample database for analysis purposes. In the script, I am querying between the sys.objects and sys.parameters system views to find all user defined functions and stored procedures with their associated parameter id, parameter name, data type, max bytes and if the parameter is an output parameter or not.
USE AdventureWorks;
GO
SELECT
SCHEMA_NAME(SCHEMA_ID) AS [Schema]
,SO.name AS [ObjectName]
,SO.Type_Desc AS [ObjectType (UDF/SP)]
,P.parameter_id AS [ParameterID]
,P.name AS [ParameterName]
,TYPE_NAME(P.user_type_id) AS [ParameterDataType]
,P.max_length AS [ParameterMaxBytes]
,P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
ORDER BY [Schema], SO.name, P.parameter_id
GO
Below is a sample result set:
Let us take a look at the columns that we selected in our script:
| Parameter Name | Explanation |
|---|---|
| <Schema> | Provides name of schema to which our UDF or SP belongs. |
| <ObjectName> | Name of UDF or SP. Name will be repeated in rows for each of its parameters. |
| <ObjectType (UDF/SP)> | Type of object. Either it will be UDF or SP. It will also be repeated in each row for each of its parameters. |
| <ParameterID> | It is unique at object level. If Object type is scalar UDF and parameterID is 0, then it represents output parameter. |
| <ParameterName> | Name assigned to parameter. |
| <ParameterDataType> | Data type assigned to parameter. |
| <ParameterMaxBytes> | Shows maximum length of parameter in bytes. If data type of parameter in with non deterministic length, e.g. It is varchar(max), nvarchar(max), varbinary(max), or xml then -1 will be returned for that parameter in this column. |
| <IsOutPutParameter> | If 1 then shows that parameter is out put parameter. If 0 then parameter is input parameter. |
Another Version
Here is another version that was contributed by Douglas E Osborne.
SELECT
SCHEMA_NAME(SO.SCHEMA_ID) AS [Schema],
SO.Name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
PM.Parameter_ID AS [ParameterID],
CASE
WHEN PM.Parameter_ID = 0 THEN 'Returns'
ELSE PM.Name
END AS [ParameterName],
TYPE_NAME(PM.User_Type_ID) AS [ParameterDataType],
CASE
WHEN TYPE_NAME(PM.User_Type_ID) IN ('float', 'uniqueidentifier', 'datetime', 'bit', 'bigint', 'int', 'image', 'money', 'xml', 'varbinary', 'tinyint', 'text', 'ntext', 'smallint', 'smallmoney') THEN ''
WHEN TYPE_NAME(PM.User_Type_ID) IN ('decimal', 'numeric') THEN '(' + CAST( PM.Precision AS VARCHAR(4) ) + ', ' + CAST( PM.Scale AS VARCHAR(4)) + ')'
WHEN PM.Max_Length = -1 THEN '(Max)'
WHEN TYPE_NAME(PM.User_Type_ID) IN ('nvarchar', 'nchar' ) THEN CAST( PM.Max_Length/2 AS VARCHAR(5))
ELSE CAST( PM.Max_Length AS VARCHAR(5))
END AS [Size],
CASE
WHEN PM.Is_Output = 1 THEN 'Output'
ELSE 'Input'
END AS [Direction]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS PM ON SO.OBJECT_ID = PM.OBJECT_ID
WHERE SO.TYPE IN ('P','FN')
ORDER BY SO.Type_Desc, [Schema], SO.Name, PM.parameter_id;
Next Steps
- This script will be very help full for cases when you take over a running database and want to analyze its functions and procedures along with parameter details.
- In SQL Server 2005 and onwards, access to metadata in catalog views is subject to securables or permissions granted. For further information in this regard please visit Metadata visibility configuration.
- For list and explanation of further available parameters in our script, please visit BOL
- Check out these related MSSQLTips:
Atif Shehzad is a passionate DBA currently serving at Pakistan Revenue Automation Limited (PRAL). His main areas of interest are SQL Server database design, best practices, performance optimization, security/permissions and Reporting Services. He blogs his SQL Server notes on DBDigger. Find him on Twitter and LinkedIn.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
Here is my updated version 12 years later ;-)
SELECT SCHEMA_NAME(SO.SCHEMA_ID) AS [Schema],
SO.Name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
PM.Parameter_ID AS [ParameterID],
CASE
WHEN PM.Parameter_ID = 0 THEN ‘Returns’
ELSE PM.Name
END AS [ParameterName],
TYPE_NAME(PM.User_Type_ID) AS [ParameterDataType],
CASE
WHEN TYPE_NAME(PM.User_Type_ID) IN (‘float’, ‘uniqueidentifier’, ‘datetime’, ‘bit’, ‘bigint’, ‘int’, ‘image’, ‘money’, ‘xml’, ‘varbinary’, ‘tinyint’, ‘text’, ‘ntext’, ‘smallint’, ‘smallmoney’) THEN ”
WHEN TYPE_NAME(PM.User_Type_ID) IN (‘decimal’, ‘numeric’) THEN ‘(‘ + CAST( PM.Precision AS VARCHAR(4) ) + ‘, ‘ + CAST( PM.Scale AS VARCHAR(4)) + ‘)’
WHEN PM.Max_Length = -1 THEN ‘(Max)’
WHEN TYPE_NAME(PM.User_Type_ID) IN (‘nvarchar’, ‘nchar’ ) THEN CAST( PM.Max_Length/2 AS VARCHAR(5))
ELSE CAST( PM.Max_Length AS VARCHAR(5))
END AS [Size],
CASE
WHEN PM.Is_Output = 1 THEN ‘Output’
ELSE ‘Input’
END AS [Direction]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS PM ON SO.OBJECT_ID = PM.OBJECT_ID
WHERE SO.TYPE IN (‘P’,’FN’)
ORDER BY SO.Type_Desc, [Schema], SO.Name, PM.parameter_id;
I love your tactic here, but an issue remains. For me too use this information from .Net, I need to know if a parameter is nullable. Would you happen to have a solution for that aside from parsing the code?