Como ver o espaço ocupado por cada tabela

Olá pessoal! Aí vai uma dica 2 em 1. A primeira dica é de uma stored procedure não documentada que existe desde o SQL Server 6.5 (veeeelho) e permite que se execute uma função, query ou stored procedure por cada tabela existente no banco de dados. A segunda dica é como usar essa stored procedure para ver o tamanho ocupado por cada tabela.

A stored procedure não documentada é a sp_MSforeachtable, que permite que você rapidamente execute uma função usando as tabelas existentes no banco como parâmetro. Caso você queira saber o count(*) de cada tabela no banco, é possível fazer isso:

exec sp_MSforeachtable ' select ''?'' as Tabela, count(*) as Registros from ? '

E para utilizar isso para ver o tamanho das tabelas, é só utilizar a procedure sp_spaceused da seguinte maneira:

exec sp_MSforeachtable 'EXEC sp_spaceused ''?'''

O resultado é apresentado no formato abaixo:

Temos o nome da tabela, número de registros, espaço reservado no datafile, espaço ocupado pelos dados, espaço ocupado pelos índices e o espaço não utilizado, ou seja, a diferença entre o que foi utilizado por dados e índices e o espaço reservado. Métricas úteis em várias situações. O problema é que as queries são executadas individualmente e não é possível fazer alguma ordenação como, por exemplo, listar as tabelas ordenadas decrescentemente por espaço em disco reservado, etc. Mas aproveito e já mostro um script para isso.

CREATE TABLE #tempTable

(

    [name] NVARCHAR(128),

    [rows] CHAR(11),

    reserved VARCHAR(18),

    data VARCHAR(18),

    index_size VARCHAR(18),

    unused VARCHAR(18)

)

insert #tempTable exec sp_MSforeachtable 'EXEC sp_spaceused ''?'''

select [name], [rows],

cast(replace(reserved, ' KB','') as int) as reserved,

cast(replace(data, ' KB','') as int) as data,

cast(replace(index_size, ' KB','') as int) as index_size,

cast(replace(unused, ' KB','') as int) as unused

into #finalTable

from #tempTable

-- Modifique o order by abaixo para ter a ordenação desejada

select * from #finalTable order by reserved desc

drop table #finalTable

drop table #tempTable

Acho esse script bem legal para ver índices sobrecarregados e também ver as tabelas mais carregadas quando tenho o primeiro contato com um banco de dados. Com frequência encontro bancos de dados com tantos índices feitos para atender uma consulta em particular que os índices ocupam muito mais espaço que os dados.

Novamente, agradeço a leitura. O que mais quero desse blog é que ele seja útil, então enviem suas dúvidas, críticas e sugestões. Ah, e se puderem clicar em um dos anúncios na página, não fico nada chateado. Consegui ganhar 80 centavos de dólar no primeiro mês, que maravilha! Abraços a todos!