Olá pessoal!
Nesse post bem rápido, gostaria de compartilhar com vocês um script simples, mostrando como listar as maiores tabelas e índices do banco de dados e retornar o tamanho de cada tabela e cada índice, podendo também, listar os objetos de todos os databases da sua instância SQL Server.

Eu já havia criado alguns artigos relacionados à tamanho dos datafiles e tamanho dos discos, mas não tinha um artigo específico para tamanho das tabelas de índices, então pensei ser hora de criar algo assim.

Também já tinha compartilhado esse mesmo script no artigo SQL Server – Consultas úteis do dia a dia do DBA que você sempre tem que ficar procurando na Internet, mas a indexação não funciona tão bem para achar esse script aqui.

Veja aqui, alguns outros artigos relacionados à espaço em disco:

Para listar as maiores tabelas do banco de dados e retornar o tamanho de cada tabela, basta executar o script abaixo no banco desejado:

SELECT TOP 100
    s.[name] AS [schema],
    t.[name] AS [table_name],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM 
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name], 
    p.[rows]
ORDER BY 
    [size_mb] DESC

Resultado:

Caso você queira listar as maiores tabelas, considerando todos os bancos de dados da sua instância, utilize o script abaixo:

IF (OBJECT_ID('tempdb..#tamanho_tabelas') IS NOT NULL) DROP TABLE #tamanho_tabelas
CREATE TABLE #tamanho_tabelas (
    [database]   NVARCHAR(256),
    [schema]     NVARCHAR(256),
    [table_name] NVARCHAR(256),
    [row_count]  BIGINT,
    [size_mb]    DECIMAL(36, 2),
    [used_mb]    DECIMAL(36, 2),
    [unused_mb]  DECIMAL(36, 2)
)

INSERT INTO #tamanho_tabelas
EXEC sys.[sp_MSforeachdb] '
IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb''))
BEGIN

    SELECT TOP 100
        ''?'' AS [database],
        s.[name] AS [schema],
        t.[name] AS [table_name],
        p.[rows] AS [row_count],
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
    FROM 
        [?].sys.tables t
        JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id]
        JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id
        LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id]
    WHERE 
        t.is_ms_shipped = 0
        AND i.[object_id] > 255 
    GROUP BY
        t.[name], 
        s.[name], 
        p.[rows]
    ORDER BY
        5 DESC
        
END'

SELECT TOP 100 * 
FROM [#tamanho_tabelas]
ORDER BY [size_mb] DESC

Resultado:

Outra necessidade muito comum também, é encontrar os maiores índices do banco para analisar se eles ainda são úteis ou se estão ocupando muito espaço desnecessariamente.

Para isso, basta utilizar o script abaixo para retornar a lista dos maiores índices do banco de dados:

SELECT TOP(100)
    s.[name] AS [schema],
    t.[name] AS [table_name],
    i.[name] AS [index_name],
    i.[type_desc],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name],
    i.[name],
    i.[type_desc],
    p.[rows]
ORDER BY 
    [size_mb] DESC

Resultado:

E se quiser realizar a mesma consulta, em todos os bancos da sua instância, utilize esse script aqui:

IF (OBJECT_ID('tempdb..#tamanho_indices') IS NOT NULL) DROP TABLE #tamanho_indices
CREATE TABLE #tamanho_indices (
    [database]   NVARCHAR(256),
    [schema]     NVARCHAR(256),
    [table_name] NVARCHAR(256),
    [index_name] NVARCHAR(256),
    [index_type] NVARCHAR(50),
    [row_count]  BIGINT,
    [size_mb]    DECIMAL(36, 2),
    [used_mb]    DECIMAL(36, 2),
    [unused_mb]  DECIMAL(36, 2)
)

INSERT INTO #tamanho_indices
EXEC sys.[sp_MSforeachdb] '
IF (''?'' NOT IN (''model'', ''master'', ''tempdb'', ''msdb''))
BEGIN

    SELECT TOP(100)
        ''?'' AS [database],
        s.[name] AS [schema],
        t.[name] AS [table_name],
        i.[name] AS [index_name],
        i.[type_desc],
        p.[rows] AS [row_count],
        CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
        CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
        CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
    FROM
        [?].sys.tables t
        JOIN [?].sys.indexes i ON t.[object_id] = i.[object_id]
        JOIN [?].sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN [?].sys.allocation_units a ON p.[partition_id] = a.container_id
        LEFT JOIN [?].sys.schemas s ON t.[schema_id] = s.[schema_id]
    WHERE 
        t.is_ms_shipped = 0
        AND i.[object_id] > 255 
    GROUP BY
        t.[name], 
        s.[name],
        i.[name],
        i.[type_desc],
        p.[rows]
    ORDER BY
        7 DESC
        
END'

SELECT TOP 100 * 
FROM [#tamanho_indices]
ORDER BY [size_mb] DESC

Resultado:

E é isso aí, pessoal!
Espero que tenham gostado dessa dica, um grande abraço e até a próxima!