something like this looks like it will work to me:
I'm gneerating a list of tables that are either the HEAP index or the clustered index shows zero rows
SELECT
'DROP TABLE ' + so.[name],
so.[name] as
, CASE
WHEN si.indid between 1 and 254
THEN si.[name]
ELSE NULL
END AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
--AND so.[name] = @tbname
AND so.type = 'U' -- Only User Tables
AND [rows] = 0
ORDER BY so.[name]
Lowell
--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!