sp_tableinfo - list tables and space usage

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


Versions etc.

SQL Server 2008 and later. 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%'