| Author |
 Topic  |
|
|
estherwu
Starting Member
3 Posts |
 Posted - 09/04/2006 : 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
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/04/2006 : 04:14:50
|
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
Sweden
29138 Posts |
Posted - 09/04/2006 : 04:27:33
|
With blank, you mean that they have no records?
Peter Larsson
Helsingborg, Sweden |
|
|
|
estherwu
Starting Member
3 Posts |
Posted - 09/04/2006 : 07:06:16
|
| Yes, blank I meant no records in the table. Thanks! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 07:10:00
|
Don't thank me, thank Chirag.
Peter Larsson
Helsingborg, Sweden |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/04/2006 : 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
Sweden
29138 Posts |
Posted - 09/04/2006 : 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 - 09/04/2006 : 22:13:06
|
Thank you all! Really appreciate your help! 
Esther |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 09/05/2006 : 11:24:50
|
Note that you should run DBCC updateusage to get proper result
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
sql blank tables
Forum Locked
