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.