How To Get Table Count In SQL Server

As a senior SQL developer, I often get the requirement to determine the number of tables in an SQL server database. I analyzed and identified a few approaches to help you achieve this task. I will walk you through all those approaches individually with examples and best practices in this article.

Approach-1: Using sys.tables

The simple approach is to use the sys.tables for this purpose. We can execute the query below.

SELECT COUNT(*) AS TableCount
FROM sys.tables
WHERE type = 'U'

After executing the above query, I got the expected output below.

Check out How To View Table In SQL Server Management Studio

Approach-2: Using INFORMATION_SCHEMA

We can also use the INFORMATION_SCHEMA for this purpose. We can use the below query for this purpose.

SELECT COUNT(*) AS TableCount
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

After executing the above query, I got the expected output, as shown in the screenshot below.

Check out How To Copy Table Schema In SQL Server

Approach-3: Using sys.databases

If you wish to get the table count from all the databases using the query below,

DECLARE @mysql NVARCHAR(MAX) = '';
SELECT @mysql = @mysql + 'SELECT ''' + name + ''' AS DatabaseName, (SELECT COUNT(*) FROM [' + name + '].sys.tables) AS TableCount UNION ALL '
FROM sys.databases
WHERE database_id > 4 AND state = 0;

SET @mysql = LEFT(@mysql, LEN(@mysql) - 10);
EXEC sp_executesql @mysql;

After executing the above query, I got the expected output below.

Check out How to Get Table Row Count in SQL Server

Approach-4: Using specific table schema

We can also get the total number of tables based on a specific schema. To do so, we can execute the below query, where the table schema name is dbo.

SELECT COUNT(*) AS TableCount
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'

After executing the above query, I got the expected output below.

Conclusion

Understanding how to get the total table count in an SQL Server is essential for a developer or DBA working in an SQL server. You can either use sys.tables, INFORMATION_SCHEMA, sys.databases, etc. Now, it’s your decision to check which approach best suits you.

You may also like following the articles below.

• How To Save Query Results As Table In SQL Server
• How to Alter Table Column to Allow NULL in SQL Server
• How to Find Table Dependencies in SQL Server