Home
About me and Nucleus
Partners
Articles, tips etc.
My blog
Links
Microsoft articles
Forums
Courses and training
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.
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%'
The code:
USE master
GO
IF OBJECT_ID('sp_tableinfo') IS NOT NULL DROP PROC sp_tableinfo
GO
CREATE PROCEDURE sp_tableinfo
@tblPat sysname = '%'
,@sort char(1) = 'm'
AS
--Written by Tibor Karaszi 2010-09-30
--Modified 2010-10-10, fixed rowcount multiplied by number of indexes.
--Modified 2010-10-11, fixed rowcount incorrect with BLOB and row overflow data.
WITH t AS
(
SELECT
SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
,SUM(CASE WHEN p.index_id IN(0,1) AND a.type_desc = 'IN_ROW_DATA' THEN p.rows ELSE 0 END) AS rows
,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9,2))) AS MB
,SUM(a.total_pages) AS pages
,ds.name AS location
FROM
sys.tables AS t
INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID
INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
WHERE t.name LIKE @tblPat
GROUP BY SCHEMA_NAME(t.schema_id), t.name, ds.name
)
SELECT schema_name, table_name, rows, MB, pages, location
FROM t
ORDER BY
CASE WHEN @sort = 'n' THEN table_name END
,CASE WHEN @sort = 'r' THEN rows END DESC
,CASE WHEN @sort = 'm' THEN MB END DESC
,CASE WHEN @sort = 's' THEN schema_name END
GO
EXEC sp_MS_Marksystemobject 'sp_tableinfo'