How to Find Large Tables in SQL Database

How to Find Large Tables in MS SQL Database
If your database appears to be larger than you expect it to be, this article will show you how to display a list of your MS SQL Database tables in size order, and list the associate size of the table.

These steps require you to have Microsoft SQL Server Management Studio installed on your computer. 
Microsoft SQL Server Mangement Studio Express 2014 is available for free from Microsoft, and can be downloaded through this link: (Click Download and select either MgmtStudio 32 bit or 64 bit, based on your computer's processor)

SQL Server Management Studio

  1. Open and log in to Microsoft SQL Server Management Studio
  2.  Right click your database
  3. Hover over Reports 
  4. Hover over Standard Reports
  5. Select Disk Usage by Top Tables

SQL Query

  1. Open and log in to Microsoft SQL Server Management Studio
  2. Click the New Query button
  3. Copy the following script into the New Query page replacing [DatabaseName] with the name of your database
    USE [DatabaseName]
    CREATE TABLE #temp (
    table_name sysname ,
    row_count INT,
    reserved_size VARCHAR(50),
    data_size VARCHAR(50),
    index_size VARCHAR(50),
    unused_size VARCHAR(50))
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,
    COUNT(*) AS col_count,
    FROM #temp a
    INNER JOIN information_schema.columns b
    ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY a.table_name, a.row_count, a.data_size
    ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
    DROP TABLE #temp
  4. Click the Execute button


Add Feedback