SQL Tips #1 - Table Row Counts & Sizes


Saturday, 12 December 2015

Share with: 
 

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

SQL, Programming
Useful SQL tips - how to list all tables and retrieve their row counts and file sizes
 
 

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
Telco Together Foundation
Cloud on Kilimanjaro