SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 sql blank tables
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

estherwu
Starting Member

3 Posts

Posted - 09/04/2006 :  04:10:04  Show Profile
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
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  04:14:50  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message


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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:27:33  Show Profile  Visit SwePeso's Homepage
With blank, you mean that they have no records?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

estherwu
Starting Member

3 Posts

Posted - 09/04/2006 :  07:06:16  Show Profile
Yes, blank I meant no records in the table. Thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  07:10:00  Show Profile  Visit SwePeso's Homepage
Don't thank me, thank Chirag.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/04/2006 :  07:10:38  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  07:48:00  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

estherwu
Starting Member

3 Posts

Posted - 09/04/2006 :  22:13:06  Show Profile
Thank you all! Really appreciate your help!

Esther
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 09/05/2006 :  11:24:50  Show Profile  Send madhivanan a Yahoo! Message
Note that you should run DBCC updateusage to get proper result


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000