Generate a parameter list for all SQL Server stored procedures and functions
Written By: Atif Shehzad -- 1/20/2009
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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:
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
- 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:
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
