In SQL Server we can query the sys.parameters system catalog view to return all parameters that belong to user-defined objects.
For system objects, we can query the sys.system_parameters view. We can alternatively query the sys.all_parameters system catalog view to return all parameters that belong to either user-defined or system objects.
Get Parameters from User-Defined Objects
Here’s an example of querying sys.parameters on the WideWorldImporters sample database to return all the parameters from user-defined objects:
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(p.object_id) AS ObjectName,
p.name AS ParameterName,
o.type_desc AS ObjectType,
TYPE_NAME(p.user_type_id) AS DataType,
p.max_length,
p.precision,
p.scale,
p.is_output,
p.has_default_value,
p.default_value
FROM sys.parameters p
JOIN sys.objects o
ON o.object_id = p.object_id
ORDER BY SchemaName, ObjectName, ParameterName;Here’s a sample of the result set:
SchemaName ObjectName ParameterName ObjectType DataType max_length precision scale is_output has_default_value default_value ------------------ -------------------------- ------------------------------------ -------------------------------- --------- ---------- --------- ----- --------- ----------------- ------------- Application AddRoleMemberIfNonexistent @RoleName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application AddRoleMemberIfNonexistent @UserName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application CreateRoleIfNonexistent @RoleName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application DetermineCustomerAccess @CityID SQL_INLINE_TABLE_VALUED_FUNCTION int 4 10 0 false false null DataLoadSimulation PopulateDataToCurrentDate @AreDatesPrinted SQL_STORED_PROCEDURE bit 1 1 0 false false null DataLoadSimulation PopulateDataToCurrentDate @AverageNumberOfCustomerOrdersPerDay SQL_STORED_PROCEDURE int 4 10 0 false false null DataLoadSimulation PopulateDataToCurrentDate @IsSilentMode SQL_STORED_PROCEDURE bit 1 1 0 false false null DataLoadSimulation PopulateDataToCurrentDate @SaturdayPercentageOfNormalWorkDay SQL_STORED_PROCEDURE int 4 10 0 false false null DataLoadSimulation PopulateDataToCurrentDate @SundayPercentageOfNormalWorkDay SQL_STORED_PROCEDURE int 4 10 0 false false null Integration GetCityUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetCityUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetCustomerUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetCustomerUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetEmployeeUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetEmployeeUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetMovementUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetMovementUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetOrderUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetOrderUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetPaymentMethodUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetPaymentMethodUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetPurchaseUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetPurchaseUpdates @NewCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null Integration GetSaleUpdates @LastCutoff SQL_STORED_PROCEDURE datetime2 8 27 7 false false null ...
In my case 66 rows were returned but I only provided a subset of these. We can always use a WHERE clause to filter the results based on a given criteria, such as the schema name, or the object, etc:
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(p.object_id) AS ObjectName,
p.name AS ParameterName,
o.type_desc AS ObjectType,
TYPE_NAME(p.user_type_id) AS DataType,
p.max_length,
p.precision,
p.scale,
p.is_output,
p.has_default_value,
p.default_value
FROM sys.parameters p
JOIN sys.objects o
ON o.object_id = p.object_id
WHERE SCHEMA_NAME(o.schema_id) = 'Application'
ORDER BY SchemaName, ObjectName, ParameterName;Result:
SchemaName ObjectName ParameterName ObjectType DataType max_length precision scale is_output has_default_value default_value ----------- -------------------------- ------------- -------------------------------- -------- ---------- --------- ----- --------- ----------------- ------------- Application AddRoleMemberIfNonexistent @RoleName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application AddRoleMemberIfNonexistent @UserName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application CreateRoleIfNonexistent @RoleName SQL_STORED_PROCEDURE sysname 256 0 0 false false null Application DetermineCustomerAccess @CityID SQL_INLINE_TABLE_VALUED_FUNCTION int 4 10 0 false false null
Parameters for System Objects
For system objects, we can change sys.parameters to sys.system_parameters, and for both system and user-defined objects we can change sys.parameters to sys.all_parameters.