How to determine the table size of a Microsoft SQL Server database

Version 4

    Option 1

    To determine the size of the tables of a Microsoft SQL Server database it is possible to use this SQL script:

     

    DECLARE @table_name sysname
    DECLARE table_list_cursor CURSOR FOR
    SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
    IF EXISTS (
    SELECT * FROM
       tempdb.INFORMATION_SCHEMA.COLUMNS
    WHERE
       table_name = '##TABLE_RES')
    BEGIN
         DROP TABLE ##TABLE_RES
    END
    CREATE TABLE ##TABLE_RES(
    Name nvarchar(255), 
    Rows int,
    reserved varchar(18),
    Data varchar(18),
    index_size varchar(18),
    Unused varchar(18))
    OPEN table_list_cursor
    FETCH NEXT FROM table_list_cursor INTO @table_name
    INSERT INTO ##TABLE_RES exec sp_spaceused @table_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
       FETCH NEXT FROM table_list_cursor INTO @table_name
       INSERT INTO ##TABLE_RES exec sp_spaceused @table_name
    END
    CLOSE table_list_cursor
    DEALLOCATE table_list_cursor
    SELECT * from ##TABLE_RES
    order by rows desc

     

     

    Option 2

    This script provides additional filtering if you do not want to see every table size in your database, but would rather only show those tables that have X# of place holders.

     

    CREATE PROCEDURE GetAllTableSizes --If you change the number of [123456789] below, change the CREATE to ALTER and run the script again
    AS
    DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
    DECLARE tableCursor CURSOR
    FOR 
    SELECT [name]
    FROM dbo.sysobjects 
    WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
    FOR READ ONLY
    CREATE TABLE #TempTable
    (
        tableName varchar(100),
        numberofRows varchar(100),
        reservedSize varchar(50),
        dataSize varchar(50),
        indexSize varchar(50),
        unusedSize varchar(50)
    )
    OPEN tableCursor
    FETCH NEXT FROM tableCursor INTO @TableName
    WHILE (@@Fetch_Status >= 0)
    BEGIN
           INSERT  #TempTable
            EXEC sp_spaceused @TableName
        FETCH NEXT FROM tableCursor INTO @TableName
    END
    CLOSE tableCursor
    DEALLOCATE tableCursor
    SELECT * 
    FROM #TempTable
    --Each [0123456789] is one charater, Change the [0123456789] to the disired number of dataSize charaters to be displayed. (Example XXXXXXXKB)
    WHERE datasize like '[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%'
    ORDER by datasize desc
    DROP TABLE #TempTable
    
    --Run after the above stored proceedure is created, run this to return table sizes
    EXEC GetAllTableSizes