I was reviewing a SQL Server database application the other day which had 100s of tables many of which I was pretty sure were not being used with the absence of any documentation i needed a way of listing all the tables and then finding out whether or not they contained data.
The following query did the trick and works on SQL Server 2000 and 2005
select so.name as tablename,
Max(si.rows) as Rows
FROM sysobjects so,
sysindexes si
WHERE so.type='U'
AND
si.id=OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY Rows DESC
Max(si.rows) as Rows
FROM sysobjects so,
sysindexes si
WHERE so.type='U'
AND
si.id=OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY Rows DESC
Update: Very useful, I've already lost track of how many times i've used this.
Copyright © 2011 Pixl8 Interactive, 3 Tun Yard, Peardon Street, London, SW8 3HT