CODEWITHSUNDEEP
databasesql-server-2008-r2
user2728106
user2728106

Reputation: 203

How to find list of tables having no records in SQL server

How to display a list of tables having no record in them and they are existing in the sql server database.Required is only to show tables with no record in them.

Upvotes: 14

Views: 24479

Answers (3)

jazb
jazb

Reputation: 5791

I found some of the previous answers still returned tables with data for me.

However, the following seems to correctly on report those with no rows (using SQL Server 2019):

SELECT schema_name(tab.schema_id) + '.' + tab.name AS [table]
FROM sys.tables tab
INNER JOIN sys.partitions part ON tab.object_id = part.object_id
WHERE part.index_id IN (
        1
        ,0
        ) -- 0 - table without PK, 1 table with PK
GROUP BY schema_name(tab.schema_id) + '.' + tab.name
HAVING sum(part.rows) = 0
ORDER BY [table]

Upvotes: 1

orgtigger
orgtigger

Reputation: 4122

Alteration 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

Upvotes: 5

marc_s
marc_s

Reputation: 754438

Try this:

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 goes to the sys.tables and other catalog views to find the tables, their indexes and partitions, to find those tables that have a row count of 0.

Upvotes: 33

Related Questions