Determining SQL Server Table Size






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.






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 : http://msdn.microsoft.com/en-us/library/ms188776.aspx


 


END


GO


EXECUTE getAllTablesSize


 





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

Comments (0)

Skip to main content