Nucleus Datakonsult sp_tableinfo - list tables and space usage

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Skor
R

Overview
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.

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:
valuemeaning
'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.