Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generate a parameter list for all SQL Server stored procedures and functions


By:   |   Read Comments (8)   |   Related Tips: More > Functions - User Defined UDF

Attend a SQL Server Conference for FREE >> click to learn more


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 which was created for SQL Server 2005 and SQL Server 2008.  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
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P','FN'))
ORDER BY [Schema], SO.name, P.parameter_id
GO

Below is a sample result set:

UDF and Sp listed

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.

 

Next Steps



Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, December 16, 2016 - 2:11:52 PM - Keith Mier Back To Top

 

I know this is old code but there are some issues with this section...

"WHERE SO.OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE TYPE IN ('P','FN'))"

1) It's needless verbose and performance intensive

"WHERE SO.TYPE IN ('P','FN')" would have accomplished the same result been easier to read and cut in half the number reads on the sys.objects table

 2) It's missing table valued functions type = 'TF'

and once you change it to "WHERE SO.TYPE IN ('P','FN', 'TF')"  the entire where clause is made redundant by the inner join on sys.parameters because only those 3 types have parameters (if you  want functions/stored procedures w/o parameters included leave in the where clause and switch the inner join to a left outer join)

TL;DR

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


Tuesday, November 20, 2012 - 6:13:43 PM - Desiree Back To Top

Thank you thank you thank you!  This was great!!!!


Friday, January 14, 2011 - 1:33:50 PM - Douglas Osborne Back To Top

Matt,

Nice tweak - 8% faster - I made one more set of changes that really dialed this in for me.

SELECT SCHEMA_NAME(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( Precision AS VARCHAR(4) ) + ', ' + CAST( Scale AS VARCHAR(4)) + ')'
ELSE CAST( PM.Max_Length AS VARCHAR(4))
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 TYPE IN ('P','FN')
ORDER BY [Schema], SO.Name, PM.parameter_id

 Doug


Friday, January 14, 2011 - 12:23:35 AM - Atif Back To Top

@Ozzie, @aprato and @Matthew, Thanks for your valuable suggestions on this topic. 

 

Regards



Thursday, January 13, 2011 - 10:32:02 AM - Matthew Back To Top

The SQL could be simplified by removing the additional subselect against sys.object. 

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
WHERE        TYPE IN ('P','FN')
ORDER BY [Schema], SO.name, P.parameter_id
GO


Thursday, March 19, 2009 - 5:49:19 PM - aprato Back To Top

 

Another place you could get this data is from the INFORMATION_SCHEMA view Parameters


select * from information_schema.parameters

Thursday, March 19, 2009 - 1:52:44 PM - Ozzie19 Back To Top

I notice that this also returns parameters for functions - here perhaps is a little improvement to handle the RETURN parameter for a function ( Parameter 0 ).

Doug

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],

CASE

WHEN P.parameter_id = 0 THEN 'Returns'

ELSE P.name

END 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
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P','FN'))
ORDER BY [Schema], SO.name, P.parameter_id


Monday, March 16, 2009 - 1:30:09 AM - murugan_san Back To Top

Very useful info


Learn more about SQL Server tools