How to list out database tables which have no records in SQL Server

Posted Date: 05 Mar 2008 Resource Type: Code Snippets Category: SQL DBA

Posted By: Shivshanker Cheral Member Level: Diamond
Rating:

Points: 10

This code sample shows how to list out database tables which have no records in SQL Server. We need to know this during the process of cleaning up of database or testing .

							

declare @strsql varchar(256)
create table #Temp  (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 #Temp (TableName, Table_rowcount) exec (@strsql)
select * from #Temp
drop table #Temp

Responses

Author: Nithya 30 Apr 2008 Member Level: Bronze Points : 2

Declare @Sql_Query nvarchar(4000)

Declare @table_name varchar(150)

Declare cursor_details cursor for select name from sys.tables where type = 'U' and name not in ('sysdiagrams', 'dtproperties')

Begin

Select @Sql_Query = ''

Open cursor_details

Fetch next from cursor_details into @table_name

While @@Fetch_Status = 0

Begin

Select @Sql_Query = @Sql_Query + 'select count(*) as TCount, ''' + @table_name + ''' as table_name from ' + @table_name + ' union '

Fetch next from cursor_details into @table_name

End

Close cursor_details

Deallocate cursor_details

Select @Sql_Query = substring(@Sql_Query, 1, len(@Sql_Query) - 6)

Print @Sql_Query

execute sp_executesql @Sql_Query

End

Author: Shivshanker Cheral 02 May 2008 Member Level: Diamond Points : 2

hi Nithya
using cursor is costlier then temp table
so i think always avoid use of cursors since these consume more resources (cpu cycles, memory)

Author: Sonu Fernandes 24 May 2008 Member Level: Gold Points : 2

Yes Shivshanker Cheral is Correct

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Searching particular Column name in all the tables in the database
	Previous Resource: How to get all the FileGroups in SQL Server
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL DBA

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

teleconferencing