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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
Hi friends, good paragraph and nice urging commented here, I am truly enjoying
by these.