Script For Dropping All Empty Tables

[19771947]

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

 

[Jamfool]

not sure if this will work as i dont have access to sql 7, but you can tweak it easy enough.

SELECT 'DROP TABLE ' + o.name, i.rowcnt 
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE indid < 2 and (OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1 
and o.name <> 'sysdiagrams'
and i.rowcnt = 0
order by 2 desc

 

[19771947]

Jamfool,

Thanks a lot..It worked fine.

Thanks for the help.