How to find empty tables in database

I was working on deployment of a new application. Part of the deployment plan was to clean certain tables and bootstrap some configuration tables. I had some SQL scripts to accomplish these tasks. But as the number of tables started growing, we were running into issues with somebody forgetting to update these set up scripts. One of the solutions we came up was to generate a report after each deployment to get list of empty tables and list of tables that have records in it. After doing some search in SQL server's documents and on news groups, I finally found example of a SQL query that provides list of empty tables. I just created a second query that gives me list of tables that have records and count of records in those tables. This approach has worked wonders to make sure that deployment is correct.

SQL query to find empty tables in database

select 'Table'=convert(char(50),t.TABLE_NAME),
      'Total records in table'=max(si.rows)
from sysindexes as si, INFORMATION_SCHEMA.TABLES as t
where t.TABLE_NAME = object_name(si.id)
      and t.TABLE_TYPE = 'BASE TABLE'
group by t.TABLE_SCHEMA, t.TABLE_NAME
HAVING max(si.rows)<=0

SQL query to find non-empty tables in database

select 'Table'=convert(char(50),t.TABLE_NAME),
      'Total records in table'=max(si.rows)
from sysindexes as si, INFORMATION_SCHEMA.TABLES as t
where t.TABLE_NAME = object_name(si.id)
      and t.TABLE_TYPE = 'BASE TABLE'
group by t.TABLE_SCHEMA, t.TABLE_NAME
HAVING max(si.rows) > 0

Search

Social

Weather

21.0 °C / 69.8 °F

weather conditions Clouds

Monthly Posts

Blog Tags