Check Tables without Any Record in a Database


–Method 1:

DECLARE @s NVARCHAR(4000)

CREATE TABLE #NoRecords

(

tablename NVARCHAR(4000),

rCnt INT

)

SELECT @s = ‘SELECT TableName, rCnt from

(select o.name as TableName, max(i.rowcnt)

as rCnt from sys.sysobjects o inner join sys.sysindexes i

on o.id = i.id where o.type = ”U”

group by o.name) t WHERE rCnt=0′

INSERT #NoRecords(TableName, rCnt)

EXEC sp_executesql @s

SELECT TableName FROM #NoRecords

DROP TABLE #NoRecords

 

–Method 2:

use mytest1

go

Create table #t (name varchar(200), rows int,

reserved varchar(200),data varchar(200)

,index_size varchar(200)

,unused varchar(200))

insert into #t

EXEC sp_MSforeachtable @command1=’ EXEC sp_spaceused ”?” ‘

 

select * from #t where rows=0

 

drop table #t

 

–Method 3:

SELECT name, rCnt FROM (SELECT b.name, SUM(A.reserved_page_count * 8) rCnt

FROM sys.dm_db_partition_stats a INNER JOIN sys.OBJECTS b

ON A.object_id = b.object_id AND b.name NOT Like ‘sys%’

GROUP BY name) t

WHERE rCnt=0

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s