Three of the system catalog views in SQL Server include sys.columns, sys.system_columns, and sys.all_columns.

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

Here’s what each one does:

sys.columns

Returns columns from user-defined objects. This includes columns from the system base tables.

sys.system_columns

Returns columns from system objects.

sys.all_columns

Returns columns from all user-defined and system objects.

In other words, the last view combines the results of the previous two views.

The following object types can have columns:

• Table-valued assembly functions (FT)
• Inline table-valued SQL functions (IF)
• Internal tables (IT)
• System tables (S)
• Table-valued SQL functions (TF)
• User tables (U)
• Views (V)

Example

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

USE Music;

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

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

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

Result:

+-----------+
| columns   |
|-----------|
| 1025      |
+-----------+
(1 row affected)
+------------------+
| system_columns   |
|------------------|
| 8982             |
+------------------+
(1 row affected)
+---------------+
| all_columns   |
|---------------|
| 10007         |
+---------------+
(1 row affected)

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

USE Music;

SELECT 
(SELECT COUNT(*) FROM sys.columns) +
(SELECT COUNT(*) FROM sys.system_columns)
AS Result;

Result:

+----------+
| Result   |
|----------|
| 10007    |
+----------+