SQL Tips #1 - Table Row Counts & Sizes

It's useful to be able to retrieve a list of your SQL tables with their row counts and size - helpful when deciding which tables require optimization or even indexing.

How it works

The query is very simple.

1. Create a temp table to store the results in, in this case named #RowCountsAndSizes
2. Execute the built-in query sp_MSForEachTable to iterate through our list of tables, inserting the results from the stored procedure sp_spaceused into our temp table
3. Return the temp table formated as we desire
4. Drop the temp table

The Query

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),
 reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18),
 unused VARCHAR(18))

EXEC sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT TableName,CONVERT(bigint,rows) AS NumberOfRows,
 CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM #RowCountsAndSizes
ORDER BY NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

The Output

The query will return results similar to those below. I've replaced my own table names with 'tablename' for privacy reasons.

tablename	NumberOfRows	SizeinKB
tablename	24515246	6678656
tablename	193248	        19672
tablename	3562	        656
tablename	2675	        592
tablename	2363	        336
tablename	437	        144
tablename	59	        88
tablename	33	        88
tablename	5	        72
tablename	5	        72

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags