How To #2 - Get a list with all empty tables from a database

You can easily find yourself in the need to know the tables with no existing rows from a database, especially in data warehousing scenarios.

The following script will help you to achieve the desired result:

SELECT t.name AS TableName
	,s.name AS SchemaName
	,SUM(p.rows) AS TotalRows
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
	ON (t.schema_id = s.schema_id)
INNER JOIN sys.partitions AS p
	ON (t.object_id = p.object_id)
-- Looks for heap based tables or for clustered indexes, but not for non-clustered indexes
WHERE p.index_id IN (0,1)
-- Use this condition if you need to filter on a particular schema
AND s.name = 'dbo'
GROUP BY t.name, s.name
HAVING SUM(p.rows) = 0;

SELECT t.name AS TableName

,s.name AS SchemaName

,SUM(p.rows) AS TotalRows

FROM sys.tables AS t

INNER JOIN sys.schemas AS s

ON (t.schema_id = s.schema_id)

INNER JOIN sys.partitions AS p

ON (t.object_id = p.object_id)

-- Looks for heap based tables or for clustered indexes, but not for non-clustered indexes

WHERE p.index_id IN (0,1)

-- Use this condition if you need to filter on a particular schema

AND s.name = 'dbo'

GROUP BY t.name, s.name

HAVING SUM(p.rows) = 0;

The query result can be further enhanced with other information as well according to your needs.

How To #2 – Get a list with all empty tables from a database

One thought on “How To #2 – Get a list with all empty tables from a database”

Leave a Reply Cancel reply