If you’re looking for a catalog view to return parameter information in SQL Server, you have a choice. In particular, you can get parameter information from sys.parameters, sys.system_parameters, and sys.all_parameters.

However, you will probably only want to use one of these views, as there are differences between them.

Here’s the official definition of each view:

sys.parameters

Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

sys.system_parameters

Contains one row for each system object that has parameters.

sys.all_parameters

Shows the union of all parameters that belong to user-defined or system objects.

In other words, the last view combines the results of the previous two views (it returns parameter information from both system and user-defined objects).

Example

Here’s an example that demonstrates the difference in results returned by these views.

USE Music;

SELECT COUNT(*) AS parameters
FROM sys.parameters;

SELECT COUNT(*) AS system_parameters
FROM sys.system_parameters;

SELECT COUNT(*) AS all_parameters
FROM sys.all_parameters;

Result:

+--------------+
| parameters   |
|--------------|
| 7            |
+--------------+
(1 row affected)
+---------------------+
| system_parameters   |
|---------------------|
| 7442                |
+---------------------+
(1 row affected)
+------------------+
| all_parameters   |
|------------------|
| 7449             |
+------------------+
(1 row affected)

In this case, there are only 7 parameters for user-defined objects in this database. The rest are from system objects.

If we add the results of the first two queries together, we get the same result as sys.all_views:

SELECT 
(SELECT COUNT(*) FROM sys.parameters) +
(SELECT COUNT(*) FROM sys.system_parameters)
AS Result;

Result:

+----------+
| Result   |
|----------|
| 7449     |
+----------+