How to Find Large Tables in SQL Database

Plesk Servers > SQL > MSSQL
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: http://www.microsoft.com/en-us/download/details.aspx?id=42299 (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]
    GO
    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))
    SET NOCOUNT ON
    INSERT #temp
    EXEC sp_msforeachtable 'sp_spaceused ''?'''
    SELECT a.table_name,
    a.row_count,
    COUNT(*) AS col_count,
    a.data_size
    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

 

Article ID: 227, , Modified: January 7, 2017 at 9:51 AM

Add Feedback

Was this article helpful?

Share this article

Print