Search for tables without data

Question

How can we get all the tables in the database without data (for example, the table has no rows) in the case of Microsoft SQL Server?
Is there any method?

Answer 1

try it

SELECT TableName=OBJECT_NAME(OBJECT_ID) ,Data_Rows= SUM(row_count) FROM sys.dm_db_partition_stats WHERE index_id in (0 ,1) GROUP BY OBJECT_ID HAVING SUM(row_count) = 0 

OR If u only need user tables, use this

  SELECT TableName=OBJECT_NAME(s.OBJECT_ID) ,Data_Rows= SUM(row_count) FROM sys.dm_db_partition_stats s JOIN sys.tables T ON T.object_id = S.object_id WHERE index_id in (0 ,1) and T.type = 'U' GROUP BY s.OBJECT_ID HAVING SUM(row_count) = 0 

Answer 2

Try it -

 WITH CTE AS ( SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY sc.name,ta.name ) SELECT * FROM CTE WHERE RowCnt = 0 

Answer 3

As stated in AdaTheDev's Answer :

I find SET FMTONLY worth mentioning:

 SET FMTONLY ON; SELECT * FROM SomeTable SET FMTONLY OFF; 

No rows are processed or sent to the client due to a request when SET FMTONLY is turned on.

The reason this might be convenient is because you can query / stored procedure and only return metadata of the result set.

Or as stated in Shoaib's answer

Try: SELECT TOP 0 * FROM [TableName]

and use SQLDataAdapter to populate the DataSet, then get the table from that DataSet.

Fiddle demo

Answer 4

To get a list of empty tables, we can use below tsql -

 EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' ' 

And to get a list of tables having at least one row of data, we can use below tsql -

  EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' ' 

Answer 5

 SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows FROM sys.tables AS t INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 and i.rows=0 

It will indicate the table name and row in the tables