none
Como listar todas as tabelas e o tamanho delas!!!

    Question

  • Boa Tarde a todos

     

    Estou tento um problema aqui com o meu banco de dados.

    Ele está crescendo absurdamente, este banco é de um ERP, na qual sem ter ninguem conectado ele está crescendo GIGAs.

    Gostaria de saber como faço para ter uma listagem de todas as tabelas e o seu respectivo tabela.

    Preciso fazer uma comparação para ver o que está fazendo o banco crescer.

    Obrigado.

     

     

     

    Monday, November 07, 2011 7:59 PM

Answers

  • Bom Dia,

    Apenas para complementar as colocações do Thiago, de fato a ausência de certas estruturas podem fazer com que a tabela fique fragmentada e aí ocupe mais espaço. Mas o que faz isso acontecer não é bem a ausência de uma chave primária, mas sim a ausência de um índice clustered. Como normalmente a chave primária é o índice clustered, a simples criação de uma chave primária evita esse efeito, mas se você criar uma chave primária não clusterizada, aí mesmo com chave primária o problema pode acontecer. A criação de chaves primárias é ao meu ver uma obrigação de toda a tabela no momento de seu nascimento, mas o índice clustered nem sempre é necessário (embora o seja na maioria dos casos).

    Quando uma tabela não possui um índice clustered, ela é chamada de HEAP TABLE (independente de ter chave primária ou não). O artigo abaixo ilustra bem o que o Thiago falou referente ao Unused.

    Heap Tables - Por que devemos evitá-las ?
    http://www.mcdbabrasil.com.br/modules.php?name=News&file=article&thold=-1&mode=flat&order=0&sid=312

    De fato o uso do cursor pode não ser tão performático, mas não creio que será uma consulta a ser executada a todo instante. Se a quantidade de tabelas em seu banco for muito grande, o uso de variáveis do tipo TABLE pode não ter um resultado tão satisfatório.

    Bem, eu sugiro então não usarmos nem variável do tipo TABLE e nem cursores. Sugiro um script mais performático então (2005 e superiores):

    SELECT
        OBJECT_NAME(object_id) As Tabela, Rows As Linhas,
        SUM(Total_Pages * 8) As Reservado,
        SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados,
            SUM(Used_Pages * 8) -
            SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice,
        SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado
    FROM
        sys.partitions As P
        INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id
    GROUP BY OBJECT_NAME(object_id), Rows
    ORDER BY Tabela

    Demorou 1 segundo em um banco com 1500 tabelas.

    Maiores detalhes em:

    Como retornar o número de linhas e o espaço ocupado por tabela – Parte III
    http://gustavomaiaaguiar.wordpress.com/2008/12/31/como-retornar-o-numero-de-linhas-e-o-espaco-ocupado-por-tabela-parte-iii/

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    Tuesday, November 08, 2011 11:26 AM
    Moderator
  • Boa Noite,

    Uma base não cresce se ninguém escrever nela. Sem ninguém estar conectado aí sim é que é difícil (é quase como uma mulher engravidar sem um homem. Tem gente que diz que é possível, mas eu nunca vi). Tente o seguinte:

    -- Declara uma tabela temporária
    -- A tabela será usada para coletar as métricas de todas as tabelas

    CREATE TABLE #Resumo (
        Name NVARCHAR(128),
        Rows CHAR(11),
        Reserved VARCHAR(18),
        Data VARCHAR(18),
        Index_Size VARCHAR(18),
        Unused VARCHAR(18))

    -- Declara uma variável para armazenar o nome da tabela
    DECLARE @Tabela NVARCHAR(128)

    -- Declara um cursor para ler todas as tabelas
    DECLARE Tabelas CURSOR
    FAST_FORWARD FOR
    SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN Tabelas

    FETCH NEXT FROM Tabelas INTO @Tabela

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #Resumo EXEC sp_spaceused @Tabela
        FETCH NEXT FROM Tabelas INTO @Tabela   
    END

    CLOSE Tabelas

    DEALLOCATE Tabelas

    -- Retorna as métricas
    SELECT Name, Rows, Reserved, Data, Index_Size, Unused FROM #Resumo

    DROP TABLE #Resumo

    Há várias outras soluções no fórum para retornar a mesma coisa. Eu sugiro procurar pela palavra "volumetria" para visualizar outras soluções igualmente interessantes.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    Monday, November 07, 2011 9:24 PM
    Moderator

All replies

  • Boa Noite,

    Uma base não cresce se ninguém escrever nela. Sem ninguém estar conectado aí sim é que é difícil (é quase como uma mulher engravidar sem um homem. Tem gente que diz que é possível, mas eu nunca vi). Tente o seguinte:

    -- Declara uma tabela temporária
    -- A tabela será usada para coletar as métricas de todas as tabelas

    CREATE TABLE #Resumo (
        Name NVARCHAR(128),
        Rows CHAR(11),
        Reserved VARCHAR(18),
        Data VARCHAR(18),
        Index_Size VARCHAR(18),
        Unused VARCHAR(18))

    -- Declara uma variável para armazenar o nome da tabela
    DECLARE @Tabela NVARCHAR(128)

    -- Declara um cursor para ler todas as tabelas
    DECLARE Tabelas CURSOR
    FAST_FORWARD FOR
    SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

    OPEN Tabelas

    FETCH NEXT FROM Tabelas INTO @Tabela

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #Resumo EXEC sp_spaceused @Tabela
        FETCH NEXT FROM Tabelas INTO @Tabela   
    END

    CLOSE Tabelas

    DEALLOCATE Tabelas

    -- Retorna as métricas
    SELECT Name, Rows, Reserved, Data, Index_Size, Unused FROM #Resumo

    DROP TABLE #Resumo

    Há várias outras soluções no fórum para retornar a mesma coisa. Eu sugiro procurar pela palavra "volumetria" para visualizar outras soluções igualmente interessantes.

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    Monday, November 07, 2011 9:24 PM
    Moderator
  • Marcelo como o Gustavo informou a procedure sp_spaceused fornece informações sobre o tamanho do banco de dados ou de tabela. Eu já tive esse problema. No meu caso foi falta de indice. No sql server se uma tabela que recebe muito registro e alteracao não tiver chave primaria ela fica com um espaco unsused gigante. Eu faria primeiramente o seguinte.

    Passo 1 - Rodaria o comando sp_spaceused primeiramente no banco de dados. (sem especificar tabela)

    Motivo: Verificar qual o motivo do crescimento do banco. Se o valor de unsused for grande você sabe que é falta de chave primária o problema. Agora pode ser o index_size (muitos indices). Pode ser também o unllocated space (ou o dba criou o banco já prevendo um espaço maior para o futuro ou o programa usa algumas tabelas para dar carga com a finalidade de gerar um relatorio ou validacao) e depois exclui os dados dela.

    Passo 2 - Dependendo do resultado uma ação diferente deveria ser utilizada.

    Ps: Se for utilizar o script do Gustavo procure trocar o cursor por uma variavel table. (boa pratica)

     


    Concatenado
    Tuesday, November 08, 2011 10:37 AM
  • Bom Dia,

    Apenas para complementar as colocações do Thiago, de fato a ausência de certas estruturas podem fazer com que a tabela fique fragmentada e aí ocupe mais espaço. Mas o que faz isso acontecer não é bem a ausência de uma chave primária, mas sim a ausência de um índice clustered. Como normalmente a chave primária é o índice clustered, a simples criação de uma chave primária evita esse efeito, mas se você criar uma chave primária não clusterizada, aí mesmo com chave primária o problema pode acontecer. A criação de chaves primárias é ao meu ver uma obrigação de toda a tabela no momento de seu nascimento, mas o índice clustered nem sempre é necessário (embora o seja na maioria dos casos).

    Quando uma tabela não possui um índice clustered, ela é chamada de HEAP TABLE (independente de ter chave primária ou não). O artigo abaixo ilustra bem o que o Thiago falou referente ao Unused.

    Heap Tables - Por que devemos evitá-las ?
    http://www.mcdbabrasil.com.br/modules.php?name=News&file=article&thold=-1&mode=flat&order=0&sid=312

    De fato o uso do cursor pode não ser tão performático, mas não creio que será uma consulta a ser executada a todo instante. Se a quantidade de tabelas em seu banco for muito grande, o uso de variáveis do tipo TABLE pode não ter um resultado tão satisfatório.

    Bem, eu sugiro então não usarmos nem variável do tipo TABLE e nem cursores. Sugiro um script mais performático então (2005 e superiores):

    SELECT
        OBJECT_NAME(object_id) As Tabela, Rows As Linhas,
        SUM(Total_Pages * 8) As Reservado,
        SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados,
            SUM(Used_Pages * 8) -
            SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice,
        SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado
    FROM
        sys.partitions As P
        INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id
    GROUP BY OBJECT_NAME(object_id), Rows
    ORDER BY Tabela

    Demorou 1 segundo em um banco com 1500 tabelas.

    Maiores detalhes em:

    Como retornar o número de linhas e o espaço ocupado por tabela – Parte III
    http://gustavomaiaaguiar.wordpress.com/2008/12/31/como-retornar-o-numero-de-linhas-e-o-espaco-ocupado-por-tabela-parte-iii/

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    Tuesday, November 08, 2011 11:26 AM
    Moderator