Home > Handy Scripts > Find table sizes in all databases

Find table sizes in all databases

Getting table sizes, row numbers and average row size per table

--sorted by table size, schema, table
DECLARE @tv_table_size TABLE (table_name SYSNAME
, num_rows INT
, reserved_size VARCHAR(20)
,
data_size VARCHAR(20)
,
index_size VARCHAR(20)
,
unused VARCHAR(20)
);
DECLARE @v_sql VARCHAR(500);
DECLARE cur CURSOR FOR
SELECT
s.name, t.name
FROM sys.schemas s INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
ORDER BY s.name, t.name
DECLARE @cv_schema_name SYSNAME, @cv_table_name SYSNAME
OPEN
cur
FETCH cur INTO @cv_schema_name, @cv_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@v_sql = @cv_schema_name + '.' + @cv_table_name
INSERT INTO @tv_table_size EXEC sp_spaceused @v_sql
FETCH cur INTO @cv_schema_name, @cv_table_name
END
CLOSE
cur
DEALLOCATE cur;

SELECT s.name AS [schema]
, t.name AS [table_name]
, CONVERT(INT, REPLACE(tv.data_size, ' KB', '')) AS [table_size_Kb]
, tv.num_rows AS [number_rows]
, CASE
WHEN tv.num_rows <> 0 THEN ROUND(CONVERT(FLOAT, REPLACE(tv.data_size, ' KB', '')) * 1024 / tv.num_rows,2)
END AS [avg_row_size_Bytes]
, REPLACE(tv.index_size, ' KB', '') AS [index_size_Kb]
, REPLACE(tv.reserved_size, ' KB', '') AS [reserved_size_Kb]
, REPLACE(tv.unused, ' KB', '') AS [unused_space_Kb]
FROM @tv_table_size tv
INNER JOIN sys.tables t ON tv.table_name = t.name
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY [table_size_Kb] DESC, s.name, t.name;

To get a detailed data, replace the above select with this one:

SELECT *
FROM @tv_table_size tv
INNER JOIN sys.tables t ON tv.table_name = t.name
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY CONVERT(INT, REPLACE(data_size, ' KB', '')) DESC, s.name, t.name;

And another way – by using undocumented sp_msforeachtable SP

(This is a good article by Gregory A. Larsen in Database Journal: SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb)

DECLARE @tv_table_size TABLE (table_name SYSNAME
, num_rows INT
, reserved_size VARCHAR(20)
,
data_size VARCHAR(20)
,
index_size VARCHAR(20)
,
unused VARCHAR(20)
);
INSERT INTO @tv_table_size EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'";
SELECT * FROM @tv_table_size ORDER BY data_size DESC, table_name;

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: