PhuocLe - Microsoft Dynamics CRM

Get size of all tables in database Oct/29/2013


Sometime you need to get size of all tables in database. This SQL code will help you.

DECLARE @tmpTable TABLE
    (
      [RowCount] INT ,
      [TableName] NVARCHAR(MAX)
    )
DECLARE @ResultTable TABLE
    (
      [Name] NVARCHAR(MAX) ,
      [Rows] INT ,
      [Reserverd] NVARCHAR(MAX) ,
      [Data] NVARCHAR(MAX) ,
      [IndexSize] NVARCHAR(MAX) ,
      [Unused] NVARCHAR(MAX)
    )
INSERT  INTO @tmpTable
        ( [RowCount] ,
          [TableName]
        )
        SELECT  [RowCount] = MAX(si.rows) ,
                [TableName] = so.name
        FROM    sysobjects so ,
                sysindexes si
        WHERE   so.xtype = 'U'
                AND si.id = OBJECT_ID(so.name)
        GROUP BY so.name
        ORDER BY 2 DESC
DECLARE @cursor CURSOR ,
    @tablename VARCHAR(MAX)
SET 
@cursor = cursor for
select [TableName] from @tmpTable
OPEN @cursor
WHILE 1 = 1 
    BEGIN
        FETCH FROM @cursor INTO @tablename
        IF @@fetch_status <> 0 
            BREAK
        INSERT  INTO @ResultTable
                ( [Name] ,
                  [Rows] ,
                  [Reserverd] ,
                  [Data] ,
                  [IndexSize] ,
                  [Unused]
                )
                EXEC sp_spaceused @tablename
    END
SELECT  [Name] ,
        ROUND(( CAST(REPLACE(data, ' KB', '') AS FLOAT) / 1024 ), 2) AS DataInMb ,
        [Rows] ,
        [Reserverd] ,
        [Data] ,
        [IndexSize] ,
        [Unused]
FROM    @ResultTable
ORDER BY ( CAST(REPLACE(data, ' KB', '') AS INT) ) DESC

And here the result after you run this SQL code.


Category: SQL
Tags:

Enter your comment

Your Name (*)
Your Email (*)
Website (*)