How to find a list of tables that do not have records on the SQL server

Question

How to display a list of tables without an entry inserted into them, and they exist in the sql server database. It is only required to show tables without writing to them.

Answer 1

Try the following:

SELECT t.NAME AS TableName, p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND p.rows = 0 GROUP BY t.Name, p.Rows ORDER BY t.Name 

The query is in sys.tables and other catalog representations to find tables, their indexes and sections, to find those tables whose row count is 0.

Answer 2

Change to add schema names:

 SELECT sch.name, t.NAME AS TableName, p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID inner Join sys.schemas sch on t.schema_id = sch.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND p.rows = 0 GROUP BY sch.name,t.Name, p.Rows ORDER BY sch.name,t.Name