Below are five methods you can use to quickly determine how many user-defined tables are in the current database in SQL Server.

All five options use the COUNT() function to get the count. Obviously, you can replace this with an asterisk (*), or the names of the columns to return a list of all user-defined tables.

Option 1 – sys.tables

The most obvious way to do this is to query the sys.tables system catalog view. The whole purpose of this view is to return a row for each user table, so it’s exactly what we need.

USE WideWorldImportersDW;
SELECT COUNT(*) AS [Number of User Tables]
FROM sys.tables;

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 29                      |
+-------------------------+

In this case I return the number of user tables from the WideWorldImportersDW database.

If I switch to another database, I get a different result:

USE Music;
SELECT COUNT(*) AS [Number of User Tables]
FROM sys.tables;

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 4                       |
+-------------------------+

As mentioned, you can list out the tables simply by replacing the COUNT(*) line with an asterisk (*) or the names of the columns:

USE Music;
SELECT name
FROM sys.tables;

Result:

+---------+
| name    |
|---------|
| Artists |
| Genres  |
| Albums  |
| Country |
+---------+

Option 2 – Filter sys.objects By Type

Another option is to query the sys.objects catalog view.

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

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 4                       |
+-------------------------+

In this case you need to filter by type = 'U' (U is for “User Table”).

Option 3 – Filter sys.objects By “Type Description”

Another way of querying the sys.objects view is to filter the results by the type_desc column.

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

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 4                       |
+-------------------------+

Option 4 – Filter sys.objects By Using the OBJECTPROPERTY() Function

You can also use the OBJECTPROPERTY() function in your WHERE clause.

This function accepts two arguments: an object ID and a property. The object ID can be the table ID and the property can be IsUserTable, 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 User Tables]
FROM sys.objects 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1;

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 4                       |
+-------------------------+

Option 5 – INFORMATION_SCHEMA.TABLES

This option queries the INFORMATION_SCHEMA.TABLES information schema view. This view returns both views and tables, so you need to filter by TABLE_TYPE = 'BASE TABLE' to return just tables.

USE Music;
SELECT COUNT(*) AS [Number of User Tables]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

Result:

+-------------------------+
| Number of User Tables   |
|-------------------------|
| 4                       |
+-------------------------+

If you need to find out the number of system tables, see 3 Ways to Count the Number of System Tables in SQL Server.