Overview
I frequently find myself writing some query to list all tables, including table size. This is a simple proc returning such information.
sp_tableinfo.sql
Versions etc.
SQL Server 2008 and later. See comment block in procedure source code for version history of the procedure.
Details
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.
Parameters
@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:
| value | meaning |
| '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%'