How to return a list of empty tables

[Al Gill]

I want to return a list of user tables from a database where the rowcount is 0. This will be a 3 step process: (1) truncate all 'New%' tables, (2) load data via ODBC/DTS into 'New%' tables, (3) list all 'New%' tables with zero rows (i.e. those that didn't get loaded, as all tables in the ODBC data source contain data).

I've tried:
select left(s2.name,32) as TableName, max(s1.rows) as Records
from sysindexes s1
inner join sysobjects s2 on s1.id=s2.id
where type = 'U' and s2.name like 'New%'
group by s2.name
HAVING max(rows) = 0
ORDER BY TableName
but of course there are multiple rows in sysindexes and the routine does not reliably return the correct list; for example the data in sysobjects & sysindexes, without the max and group by, might look like:
TableName Rows
NewARTxn 0
NewARTxn 1214800
NewARTxn 1214800
NewARTxn 1214800
NewARTxn 1214800
NewARTxn 1214800
NewARTxn 1214800
I was hoping to come up with a single T-SQL statement that I could use in an xp_sendmail situation to email me the results.

Thanks for the suggestions.

Al

[Markus]

Hi Al,

is this what your looking for ?

select o.Name , Rows from
sysobjects o join sysindexes i on o.id = i.id
where xtype = 'U' and o.name LIKE 'New%'
and indid < 2 and rows =0

[philmac]

select i.name , i.rows
from sysindexes i
where i.indid < 2
and i.rows=0

[Al Gill]

Thanks everyone; indeed 'indid' did the trick.