Table and Index size in SQL Server

Can we have a SQL query which will basically help in viewing table and index sizes in SQl Server.

How SQL server maintains memory usage for tables/indexes?

  • SQL Server Script Generator CAST datetime values from hex
  • What is wrong with my Try Catch in T-SQL?
  • how to select all columns but one in SQL Server 2012?
  • Does sorting happens using distinct clause
  • Common list of SQL exceptions with numbers
  • Connect internally to a Virtual Machine with SQL Server from Web Sites using Windows Azure
  • 8 Solutions collect form web for “Table and Index size in SQL Server”

    The exec sp_spaceused without parameter shows the summary for the whole database. The foreachtable solution generates one result set per table – which SSMS might not be able to handle if you have too many tables.

    I created a script which collects the table infos via sp_spaceused and displays a summary in a single record set, sorted by size.

    create table #t
    (
      name nvarchar(128),
      rows varchar(50),
      reserved varchar(50),
      data varchar(50),
      index_size varchar(50),
      unused varchar(50)
    )
    
    declare @id nvarchar(128)
    declare c cursor for
    select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'
    
    open c
    fetch c into @id
    
    while @@fetch_status = 0 begin
    
      insert into #t
      exec sp_spaceused @id
    
      fetch c into @id
    end
    
    close c
    deallocate c
    
    select * from #t
    order by convert(int, substring(data, 1, len(data)-3)) desc
    
    drop table #t
    

    sp_spaceused gives you the size of all the indexes combined.

    If you want the size of each index for a table, use one of these two queries:

    SELECT
        i.name                  AS IndexName,
        SUM(s.used_page_count) * 8   AS IndexSizeKB
    FROM sys.dm_db_partition_stats  AS s 
    JOIN sys.indexes                AS i
    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    WHERE s.[object_id] = object_id('dbo.TableName')
    GROUP BY i.name
    ORDER BY i.name
    
    SELECT
        i.name              AS IndexName,
        SUM(page_count * 8) AS IndexSizeKB
    FROM sys.dm_db_index_physical_stats(
        db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
    JOIN sys.indexes AS i
    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    GROUP BY i.name
    ORDER BY i.name
    

    The results are usually slightly different but within 1%.

    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
    

    On SQL 2012 getting this information on a table level has become deliciously simple:

    SQL Management Studio -> Right click on Db -> Reports -> Standard Reports -> Disk usage by table !

    Enjoy

    Here is more compact version of the most successful answer:

    create table #tbl(
      name nvarchar(128),
      rows varchar(50),
      reserved varchar(50),
      data varchar(50),
      index_size varchar(50),
      unused varchar(50)
    )
    
    exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'
    
    select * from #tbl
        order by convert(int, substring(data, 1, len(data)-3)) desc
    
    drop table #tbl
    

    it’s being a long time since the creation for this post but I wanted to share my script:

    WITH CteIndex
    AS
    (
    SELECT 
         reservedpages = (reserved_page_count)
         ,usedpages = (used_page_count)
         ,pages = (
                CASE
                    WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE lob_used_page_count + row_overflow_used_page_count
                END
                )    
         ,s.object_id   
         ,i.index_id        
         ,i.type_desc AS IndexType
         ,i.name AS indexname
        FROM sys.dm_db_partition_stats s
        INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id   
    )
    SELECT DISTINCT
    DB_NAME(DB_ID()) AS DatabaseName
    ,o.name AS TableName
    ,o.object_id
    ,ct.indexname
    ,ct.IndexType
    ,ct.index_id
    , IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN  pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
    FROM CteIndex ct
    INNER JOIN sys.objects o ON o.object_id = ct.object_id
    INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
    AND ps.index_id = ct.index_id
    ORDER BY name ASC
    

    it works for :

    • SQL Server (starting with 2008)
    • Includes info for all tables per current database
    --Gets the size of each index for the specified table
    DECLARE @TableName sysname = N'SomeTable';
    
    SELECT i.name AS IndexName
          ,8 * SUM(s.used_page_count) AS IndexSizeKB
    FROM sys.indexes AS i
        INNER JOIN sys.dm_db_partition_stats AS s 
            ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
    WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
    GROUP BY i.name
    ORDER BY i.name;
    
    SELECT i.name AS IndexName
          ,8 * SUM(a.used_pages) AS IndexSizeKB
    FROM sys.indexes AS i
        INNER JOIN sys.partitions AS p 
            ON i.[object_id]  = p.[object_id] AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units AS a 
            ON p.partition_id = a.container_id
    WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
    GROUP BY i.name
    ORDER BY i.name;
    

    There is an extended stored procedure sp_spaceused that gets this information out. It’s fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.