Three of the system catalog views in SQL Server include sys.views, sys.system_views, and sys.all_views.

These three catalog views all provide metadata about views in the database, but there’s a subtle difference between each view.

Here’s what each one does:

sys.views

Returns all user-defined views.

sys.system_views

Returns all system views shipped with SQL Server.

sys.all_views

Shows all user-defined and system views.

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

Example

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

USE Music;

SELECT COUNT(*) AS [User Defined]
FROM sys.views;

SELECT COUNT(*) AS [System]
FROM sys.system_views;

SELECT COUNT(*) AS [All Views]
FROM sys.all_views;

Result:

+----------------+
| User Defined   |
|----------------|
| 3              |
+----------------+
(1 row affected)
+----------+
| System   |
|----------|
| 494      |
+----------+
(1 row affected)
+-------------+
| All Views   |
|-------------|
| 497         |
+-------------+
(1 row affected)

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

USE Music;

SELECT 
(SELECT COUNT(*) FROM sys.views) +
(SELECT COUNT(*) FROM sys.system_views)
AS Result;

Result:

+----------+
| Result   |
|----------|
| 497      |
+----------+