In SQL Server the sys.sql_modules, sys.system_sql_modules, and sys.all_sql_modules system catalog views return metadata about SQL language-defined modules in SQL Server.

However, there is a difference between them.

Here’s the official definition of each view:

sys.sql_modules

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view.

sys.system_sql_modules

Returns one row per system object that contains an SQL language-defined module. System objects of type FN, IF, P, PC, TF, V have an associated SQL module.

sys.all_sql_modules

Returns the union of sys.sql_modules and sys.system_sql_modules.

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

Example

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

USE Music;

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

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

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

Result:

+---------------+
| sql_modules   |
|---------------|
| 9             |
+---------------+
(1 row affected)
+----------------------+
| system_sql_modules   |
|----------------------|
| 2020                 |
+----------------------+
(1 row affected)
+-------------------+
| all_sql_modules   |
|-------------------|
| 2029              |
+-------------------+
(1 row affected)

In this case, there are only 9 user-defined modules in this database. The rest are from system modules.

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

SELECT 
(SELECT COUNT(*) FROM sys.sql_modules) +
(SELECT COUNT(*) FROM sys.system_sql_modules)
AS Result;

Result:

+----------+
| Result   |
|----------|
| 2029     |
+----------+