return tables with 0 rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

return tables with 0 rows

Author

Topic

optimist
Starting Member

1 Post

Posted - 2006-11-20 : 11:26:11

Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 11:34:36

This should do it

SELECT o.[name] 
FROM sysindexes i
INNER JOIN sysobjects o on i.[id] = o.[id]
  WHERE indid < 2 and [rows] = 0 and o.[type] = 'U'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-20 : 11:42:18

of course this would require for all tables to have indexes and their statistics to be up to date.

you could also use an undocumented stored procedure:
exec sp_msforeachtable 'select ''?'', count(*) from ?'

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 11:45:30

Here's one way...you may need to add the table owner though


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE x (TABLE_NAME sysname, [Count] int)
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHILE @TABLE_NAME IS NOT NULL
  BEGIN
	SELECT @sql = 'INSERT INTO x(TABLE_NAME, [Count]) SELECT '''+@TABLE_NAME+''', COUNT(*) FROM [' + @TABLE_NAME+']'
	EXEC(@sql)	
	SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
  END

SELECT TABLE_NAME, [Count] FROM x -- WHERE [Count] = 0
GO

SET NOCOUNT OFF
DROP TABLE x
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 12:26:28

quote:
of course this would require for all tables to have indexes and their statistics to be up to date.

No, that's not true. All tables have an entry in sysindexes, even those that do not have an index.
As for statistics being up to date, just run sp_updatestats before you run the query. Your stats should be up to date anyway - and the row count is pretty reliable anyway, it usually only is wrong if you've performed bulk operations or other non standard operations on the table. If you have some large tables, running a count(*) on each of them will be a lot slower than querying sysindexes.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-20 : 12:27:55

cool. thanx.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-20 : 14:24:02

Well mine might run longer, but it will be accurate...well as accurate as it is at the time of execution

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sanjayanthan
Starting Member

5 Posts

Posted - 2006-11-21 : 18:51:55

Expert answer see the below link

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188953,00.html

sanjayanthan

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-21 : 20:34:50

quote:
Originally posted by sanjayanthan

Expert answer see the below link

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188953,00.html

sanjayanthan

How is that different?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 21:52:41

quote:
Originally posted by sanjayanthan

Expert answer see the below link

http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188953,00.html

sanjayanthan

This uses dynamic SQL completely unnecessarily, rather use one of the ways you see here - so much for the "expert" answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources