Here’s an article that presents three ways to quickly determine how many system tables are in the current database in SQL Server.

All three options use the COUNT() function while querying the sys.objects system catalog view. They all result in the same output, so you really don’t need to go past the first option. But I’ll list them anyway.

Option 1 – By Type

The most concise way to do this is filter by the type column.

USE Music;
SELECT COUNT(*) AS [Number of System Tables]
FROM sys.objects 
WHERE type = 'S';

Result:

+---------------------------+
| Number of System Tables   |
|---------------------------|
| 72                        |
+---------------------------+

Here, I filter by a type of S. The S stands for “System base table”.

If I switch to the master database, I get a different result:

USE Master;
SELECT COUNT(*) AS [Number of System Tables]
FROM sys.objects 
WHERE type = 'S';

Result:

+---------------------------+
| Number of System Tables   |
|---------------------------|
| 79                        |
+---------------------------+

This is because the master database contains some system tables that aren’t in other databases.

We can run the following query to get the names:

USE master;
SELECT name 
FROM sys.objects
WHERE type = 'S' 
AND name NOT IN (
  SELECT name FROM model.sys.objects WHERE type = 'S' 
);

Result:

+--------------------------+
| name                     |
|--------------------------|
| sysextendedrecoveryforks |
| syslogshippers           |
| sysmatrixageforget       |
| sysmatrixages            |
| sysmatrixbricks          |
| sysmatrixconfig          |
| sysmatrixmanagers        |
+--------------------------+

In this case I compare the master database to the model database. You can explicitly specify a different database by swapping model with the name of the other database.

Option 2 – By “Type Description”

Another option is to filter by the type_desc column instead of the type column.

USE Music;
SELECT COUNT(*) AS [Number of System Tables]
FROM sys.objects 
WHERE type_desc = 'SYSTEM_TABLE';

Result:

+---------------------------+
| Number of System Tables   |
|---------------------------|
| 72                        |
+---------------------------+

Option 3 – By OBJECTPROPERTY()

If for some reason you find the previous two options unsuitable, you could try using the OBJECTPROPERTY() function.

This function accepts two arguments: an object ID and a property. The object ID can be the table ID and the property can be IsSystemTable, which determines whether or not the object is a system table.

Therefore, you could do something like this:

USE Music;
SELECT COUNT(*) AS [Number of System Tables]
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, 'IsSystemTable') = 1;

Result:

+---------------------------+
| Number of System Tables   |
|---------------------------|
| 72                        |
+---------------------------+

List the Tables

You can modify the three options if want to list the tables instead of just counting them. To do this, simply replace the COUNT(*) AS [Number of User Tables] with *. Alternatively, you can explicitly name the columns you want returned.

Count User-Defined Tables

If you need to find out the number of user tables, see 5 Ways to Count the Number of User-Defined Tables in SQL Server.