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

Version 6

    Option 1 - Via SMSS GUI

     

    Right-click the database in question and select "Reports" -> "Standard Reports" -> "Data usage by top tables".

     

    Option 2 - Via SQL Query

    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 3 - Via SQL Query showing only the tables with X number of placeholders

    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 againAS 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 sizesEXEC GetAllTableSizes