Home Forums SQL Server 2008 T-SQL (SS2K8) query to get the size of all indexes in a database RE: query to get the size of all indexes in a database

  • You could do something like this:

    IF OBJECT_ID('tempdb..#indexInfo') IS NOT NULL

    DROP TABLE #indexInfo;

    SELECT TOP 0

    t.TABLE_CATALOG AS db,

    t.TABLE_SCHEMA AS SchemaName,

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    ISNULL(i.name,'<HEAP>') AS IndexName,

    i.index_id AS IndexID,

    8 * SUM(a.used_pages) AS 'Indexsize(KB)'

    INTO #indexInfo

    FROM sys.indexes AS i

    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    LEFT JOIN INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME

    WHERE t.TABLE_SCHEMA IS NOT NULL

    GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name

    ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;

    EXEC sp_msforeachdb '

    USE [?];

    INSERT INTO #indexInfo

    SELECT t.TABLE_CATALOG AS db,

    t.TABLE_SCHEMA AS SchemaName,

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    ISNULL(i.name,''<HEAP>'') AS IndexName,

    i.index_id AS IndexID,

    8 * SUM(a.used_pages) AS ''Indexsize(KB)''

    FROM [?].sys.indexes AS i

    JOIN [?].sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

    JOIN [?].sys.allocation_units AS a ON a.container_id = p.partition_id

    LEFT JOIN [?].INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME

    WHERE t.TABLE_SCHEMA IS NOT NULL

    GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name

    ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;';

    SELECT ISNULL(db,'<ALL DB>') AS db,

    SchemaName,

    TableName,

    IndexName,

    IndexID,

    [indexsize(KB)]

    FROM #indexInfo

    WHERE [Indexsize(KB)]<>0

    ORDER BY db,SchemaName,TableName,IndexName,IndexID;

    Note: I based my script on this: http://blog.sqlauthority.com/2010/05/09/sql-server-size-of-index-table-for-each-index-solution-2/[/url].

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001