By: Atif Shehzad | Updated: 2022-02-23 | Comments (10) | Related: > Functions User Defined UDF
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:
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-02-23
