sql blank tables - 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

Old Forums

CLOSED - General SQL Server

sql blank tables

Author

Topic

estherwu
Starting Member

3 Posts

Posted - 2006-09-04 : 04:10:04

is there a quick way to get a list of table (table names) that are not blank in the database?? I just need the table names.

I have a database with about 500 tables, and I need to know which ones of these 500 are blank and which ones are not...instead of open them up one by one to check, any quick way to do this?

Thanks!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 04:14:50

[code]

SELECT o.[name], i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2
And i.rowcnt >1
[/code]

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:27:33

With blank, you mean that they have no records?

Peter Larsson
Helsingborg, Sweden

estherwu
Starting Member

3 Posts

Posted - 2006-09-04 : 07:06:16

Yes, blank I meant no records in the table. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 07:10:00

Don't thank me, thank Chirag.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-04 : 07:10:38

Like this?


SELECT o.[name], i.rowcnt
FROM sysobjects o
LEFT OUTER JOIN sysindexes i
ON o.[id] = i.[id]
WHERE o.xtype = 'U' AND i.indid < 2 
And i.rowcnt <1

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 07:48:00

Or if you prefer having it all directly, using Chirag's solution


SELECT		o.[name] TableName,
		CASE WHEN i.rowcnt = 0 THEN 'Blank' ELSE 'Not blank' END Status
FROM		sysobjects o
LEFT JOIN	sysindexes i ON o.[id] = i.[id] WHERE o.xtype = 'U' AND i.indid < 2

Peter Larsson
Helsingborg, Sweden

estherwu
Starting Member

3 Posts

Posted - 2006-09-04 : 22:13:06

Thank you all! Really appreciate your help!