Nucleus Datakonsult sp_tableinfo - list tables and space usage


About me and Nucleus

Articles, tips etc.
My blog
Microsoft articles

Courses and training

Microsoft Most Valuable Professional


I frequently find myself writing some query to list all tables, including table size. This is a simple proc returning such information.

Versions etc.
This script was written and tested on SQL Server 2008 R2. It should work on 2008 and 2005 as well.
See comment block in procedure source code for version history of the procedure.

The procedure returns a row for each table in current database (unless table spread over several filegroups, using several indexes or partitions; if so then several rows are returned). It returns schema name, table name, number of rows, size in both MB and pages and file group.

@tblPat sysname '%' 
Name of table. Default is all tables in current database. Wildcards accepted (LIKE used in query).

@sort char(1) 'm'
The @sort parameter is used for sorting the result. The value is a single letter:
'n'table name
'r'number of rows
'm'space usage (default)
's'schema name

Usage examples
EXEC sp_tableinfo
EXEC sp_tableinfo DEFAULT 'n'
EXEC sp_tableinfo 'd'
EXEC sp_tableinfo '%pers%'

The code:
You find the code here.