Script For Dropping All Empty Tables

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Script For Dropping All Empty Tables

Author

Topic

dbaman
Starting Member

46 Posts

Posted - 2006-08-08 : 09:50:27

Hello All,

I have SQLServer 7. I want to prepare/generate the script so that I can drop all the user's table with 0 record count.

Please share if you have any such script.

Thnaks,

R

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-08 : 10:45:17

I'll give u the script to find tables with 0 records.

U better write the rest of the code (for ur skills to be developed

), to create the code for dropping.

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 > 0
ORDER BY o.[name]

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-08 : 13:15:52

or

Select object_name(id) from sysindexes
where indid<2 and rows=0

But you need to use DBCC Updateusage to get correct count

Madhivanan

Failing to plan is Planning to fail

dbaman
Starting Member

46 Posts

Posted - 2006-08-08 : 14:58:36

Srinika and madhivanan,

Thanks a lot...I made some adjustment to suit to my environment and got the job done. Thanks guys.

R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources