«

»

jun 14

Espaço utilizado por cada tabela

Hoje precisei fazer um levantamento do espaço utilizado por cada tabela da base. Encontrei o script abaixo no post do Greg Robidoux do MSSQL Tips:

 

BEGIN try  
DECLARE @table_name VARCHAR(500) ;  
DECLARE @schema_name VARCHAR(500) ;  
DECLARE @tab1 TABLE( 
        tablename VARCHAR (500) collate database_default 
,       schemaname VARCHAR(500) collate database_default 
);  
DECLARE  @temp_table TABLE (     
        tablename sysname 
,       row_count INT 
,       reserved VARCHAR(50) collate database_default 
,       data VARCHAR(50) collate database_default 
,       index_size VARCHAR(50) collate database_default 
,       unused VARCHAR(50) collate database_default  
);  

INSERT INTO @tab1  
SELECT t1.name 
,       t2.name  
FROM sys.tables t1  
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );    

DECLARE c1 CURSOR FOR  
SELECT t2.name + ‘.’ + t1.name   
FROM sys.tables t1  
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );    

OPEN c1;  
FETCH NEXT FROM c1 INTO @table_name; 
WHILE @@FETCH_STATUS = 0  
BEGIN   
        SET @table_name = REPLACE(@table_name, ‘[‘,”);  
        SET @table_name = REPLACE(@table_name, ‘]’,”);  

        — make sure the object exists before calling sp_spacedused 
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name)) 
        BEGIN 
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ; 
        END 

        FETCH NEXT FROM c1 INTO @table_name;  
END;  
CLOSE c1;  
DEALLOCATE c1;  
SELECT t1.* 
,       t2.schemaname  
FROM @temp_table t1  
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) 
ORDER BY  schemaname,tablename; 
END try  
BEGIN catch  
SELECT -100 AS l1 
,       ERROR_NUMBER() AS tablename 
,       ERROR_SEVERITY() AS row_count 
,       ERROR_STATE() AS reserved 
,       ERROR_MESSAGE() AS data 
,       1 AS index_size, 1 AS unused, 1 AS schemaname  
END catch

 

Resultado:

 

 

 

Desta forma é possível saber o tamanho real das tabelas, quanto estão ocupando no disco, quantas linhas possuem, etc..

4 comentários

Pular para o formulário de comentário

  1. Roberto Gentile

    Oi Mercante, como vai?

    Uma pequena correção, no ORDER BY, substituir pela instrução abaixo, pois deu como “Msg 209, Level 16, State 1, Line 49 – Nome da coluna ‘tablename’ ambíguo.”:

    ORDER BY schemaname,t1.tablename;

    Valeu pelo script, muito útil!

    Abs,

    Roberto Gentile

  2. Luiz Mercante

    Olá Roberto!

    Fiquei pensando aqui e não consegui entender porque não foi possível relacionar o nome da tabela no seu servidor. De qualquer forma agradeço o comentário e deixo aqui para que caso algum outro usuário não consiga utilizar aproveite sua correção.

    Abs!

  3. Angelo Maximo

    ótimo post Mercante. Testei com sucesso, sem precisar alterar o ORDER BY.
    Parabéns.

  4. William Maxwel

    Bom dia Pessoal,

    Rodei a sql aqui e precisei colocar o Order By conforme o Roberto mencionou. Executei o procedimento no SQL Server Express 2008.

    A SQL completa sem erros fica da seguinte forma:

    BEGIN try
    DECLARE @table_name VARCHAR(500) ;
    DECLARE @schema_name VARCHAR(500) ;
    DECLARE @tab1 TABLE(
    tablename VARCHAR (500) collate database_default
    , schemaname VARCHAR(500) collate database_default
    );
    DECLARE @temp_table TABLE (
    tablename sysname
    , row_count INT
    , reserved VARCHAR(50) collate database_default
    , data VARCHAR(50) collate database_default
    , index_size VARCHAR(50) collate database_default
    , unused VARCHAR(50) collate database_default
    );

    INSERT INTO @tab1
    SELECT t1.name
    , t2.name
    FROM sys.tables t1
    INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

    DECLARE c1 CURSOR FOR
    SELECT t2.name + ‘.’ + t1.name
    FROM sys.tables t1
    INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

    OPEN c1;
    FETCH NEXT FROM c1 INTO @table_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @table_name = REPLACE(@table_name, ‘[‘,”);
    SET @table_name = REPLACE(@table_name, ‘]’,”);

    — make sure the object exists before calling sp_spacedused
    IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
    BEGIN
    INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
    END

    FETCH NEXT FROM c1 INTO @table_name;
    END;
    CLOSE c1;
    DEALLOCATE c1;
    SELECT t1.*
    , t2.schemaname
    FROM @temp_table t1
    INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
    ORDER BY schemaname,t1.tablename;
    END try
    BEGIN catch
    SELECT -100 AS l1
    , ERROR_NUMBER() AS tablename
    , ERROR_SEVERITY() AS row_count
    , ERROR_STATE() AS reserved
    , ERROR_MESSAGE() AS data
    , 1 AS index_size, 1 AS unused, 1 AS schemaname
    END catch

    Resultado:

Deixe uma resposta