Get table sizes painlessly

Here is a handy script to get a quick look at table sizes for all tables in your database without putting unneeded load on a production server. It has no detrimental effect on performance. It gets row and page counts from the sysindexes table without having to touch your user tables. These counts are not exact but they are accurate enough for most purposes. The data is updated each time your statistics are updated and is usually within a fraction of a percent of the current values.

I use this query for a number of purposes. When I take a first look at a database, it gives me an idea of the size of the major tables. When performance tuning, I sort it by table name so I can quickly find the relative sizes of the tables in the query I am trying to optimize. It is easy to locate tables that were created for some purpose but never used. You can also use it to record database growth by taking snapshots at regular intervals.

I did not write this code as a stored procedure because in my work as a consultant, often I cannot create objects on the client’s production SQL Server. Scripts are handier and easier to modify for particular situations. You, however are free to enclose it in a stored procedure if that is better for you.

There is nothing clever about the code, but it is useful. The query returns the table name, the number of rows it contains, and the number of 8kb pages it contains, including pages used to store binary large objects like ntext and image data. It will run on all versions of SQL Server.

If Microsoft ever makes good on its threat to eliminate direct access to the system tables, then we will have to create a version that selects from the sys.objects and sys.indexes views. But right now it is simpler to have a single query that works on all versions.

-- GetTableSizes.sql

select o.name
, sum(i.rows) as rows
, sum(case when indid < 2 then i.dpages
when indid=255 then i.used
else 0 end) as allpages
from sysobjects o
join sysindexes i on o.id = i.id
where (indid < 2 or indid = 255)
and o.name not like 'sys%'
group by o.name, indid
order by rows desc