Introduction

This script uses a temp table to store the results of the query. It sorts the results in descending order – showing the largest tables on top.

CREATE TABLE #FindLargeTables 

(

  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 #FindLargeTables

 

EXEC sp_msforeachtable 'sp_spaceused ''?'''

 

SELECT 

  a.table_name,

  a.row_count,

  COUNT(*) AS col_count,

  a.data_size

  FROM #FindLargeTables 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 #FindLargeTables

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin