Calculate SQL Table Size/Record

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
                      where A .ROW_COUNT > 0
    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

Leave a Reply

Your email address will not be published.