Determining SQL Server Table Size

  • Article

Determining SQL Server Table Size

There a few days, I had to identify areas of my database that were taking up the most physical storage space.

The process that the stored procedure goes through is very simple. I create a temporary table to store the individual data elements for each table.

The Script has been tested and used on a SQL Server 2005 and 2008 instance to display the sizes of SQL Server's Database's Tables.

Déterminer la taille des tables de votre base de données SQL Server

Il y a quelques jours, j'ai dû identifier les zones de ma base de données qui consommaient le plus d'espace physiques.

Ce script est assez simple. Je vais simplement créer une table temporaire et stocker le résultat de chaque table.

Le script a été testé et utilisé sur une instance SQL Server 2005 et 2008 pour afficher la taille des tables de ma base de données de SQL Server.

Determinar el tamaño de las tablas de base de datos de SQL Server

Hace unos días, tuve que identificar las áreas de mi base de datos que más espacio de almacenamiento consumían.

Este script es bastante simple. Se crea una tabla temporal para almacenar los elementos de datos individuales para cada tabla.

Este script ha sido probado y utilizado en una instancia SQL Server 2005 y 2008 para mostrar los tamaños de las tablas de mi base de datos de SQL Server.

CREATE PROCEDURE getAllTablesSize

AS

BEGIN

      DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

      CREATE TABLE

            #temp (

                  [name] varchar(250),

                  [rows] varchar(50),

                  [reserved] varchar(50),

                  [data] varchar(50),

                  [index_size] varchar(50),

                  [unused] varchar(50)

                  );

      INSERT #temp EXEC ('sp_msforeachtable ''sp_spaceused ''''?''''''');

      UPDATE

            #temp

      SET

            [rows] = LTRIM(RTRIM(REPLACE(t.rows,'KB',''))),

            [reserved] = LTRIM(RTRIM(REPLACE(t.reserved,'KB',''))),

            [data] = LTRIM(RTRIM(REPLACE(t.data,'KB',''))),

            [index_size] = LTRIM(RTRIM(REPLACE(t.index_size,'KB',''))),

            [unused] = LTRIM(RTRIM(REPLACE(t.unused,'KB','')))

      FROM #temp AS t

      SELECT

            SUM(CAST([reserved] as decimal))/1024 AS 'Total reserved MB',

            SUM(CAST([data] as decimal))/1024 AS 'Total data MB',

            SUM(CAST([index_size] as decimal))/1024 AS 'Total index_size MB',

            SUM(CAST([unused] as decimal))/1024 AS 'Total unused MB'

      FROM

            #temp

      SELECT

            [name] ,

            CAST([rows] as INT)'rows' ,CAST([reserved] as INT)/1024 'reserved MB',

            CAST([data] as INT)/1024 'data MB' ,

            CAST([index_size]/1024 as INT)'index_size MB',

            CAST([unused] as INT)/1024 'unused MB'

      FROM

            #temp

      ORDER BY

            CAST(reserved as INT) DESC

      DROP TABLE #temp;

      -- rows : Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.

      -- reserved : Total amount of reserved space for objname.

      -- data : Total amount of space used by data in objname.

      -- index_size : Total amount of space used by indexes in objname.

      -- unused : Total amount of space reserved for objname but not yet used.

      -- unused : Total amount of space reserved for objname but not yet used.

      -- More detail here : https://msdn.microsoft.com/en-us/library/ms188776.aspx

END

GO

EXECUTE getAllTablesSize

Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |