Wednesday, March 25, 2009

How can I list out database tables which have no records?

You can make your solution more flexible by grabbing the table names from the sysobjects table:

declare @strsql varchar(256)
create table #emptytables (tablename varchar(128), table_rowcount int)

select @strsql='select distinct o.name as TableName, x.rowcnt as Table_RowCount
from sysobjects o
inner join sysindexes x
on o.id = x.id
where x.rowcnt = 0 and
o.type = ''U'''

insert #emptytables (TableName, Table_rowcount) exec (@strsql)
select * from #emptytables
drop table #emptytables