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?
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.
Below is a sample result set:
Let us take a look at the columns that we selected in our script:
Provides name of schema to which our UDF or SP belongs.
Name of UDF or SP. Name will be repeated in rows for each of its parameters.
Type of object. Either it will be UDF or SP. It will also be repeated in each row for each of its parameters.
It is unique at object level. If Object type is scalar UDF and parameterID is 0, then it represents output parameter.
Name assigned to parameter.
Data type assigned to parameter.
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.
If 1 then shows that parameter is out put parameter. If 0 then parameter is input parameter.
- 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 on wards, 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
- To download sample databases for SQL Server 2005 please go to SQL Server 2005 sample DB
- To download sample databases for SQL Server 2008 please go to SQL Server 2008 sample DB
- Check out these related MSSQLTips:
Last Update: 2009-01-20
About the author
View all my tips