[SQL Code] Mapeando tabelas e seus respectivos Filegroups

Olá pessoal,

Aqui na empresa nós temos um controle manual de todo o crescimento dos bancos de dados e seus arquivos, processo que chamamos de Volumetria. O tamanho máximo de cada banco de dados é controlado manualmente, ou seja, não deixamos habilitada a propriedade AUTOGROW dos bancos de dados.

Nosso cliente pediu para expandir o tamanho de um banco de dados que possui 4 Filegroups e para isso perguntamos quais tabelas iriam crescer para expandir os arquivos correspondentes ao Filegroup com a(s) tabela(s) que iriam crescer. A resposta do cliente foi: Quê?! Hã!?! ou poderia ser ainda um tremendo WTF!!!!???!?

Em resumo, os analistas da aplicação não sabiam dizer onde cada tabela estava localizada então, vamos ao nosso script.

Uma maneira bem simplista de verificar em que Filegroup uma tabela está localizada é executando a procedure sp_help, assim:

USE <seu database>
EXEC sp_help <sua tabela>

O único problema de se fazer isto é quando se tem 317 tabelas para verificar! Logo, é hora de fazer um script para isto. =D

Depois de pesquisar as System Views do SQL Server, cheguei neste resultado:

SELECT
    T.NAME AS TABLE_NAME,
    P.ROWS,
    FG.NAME AS FILEGROUP_NAME,
    FG.IS_DEFAULT
FROM
    SYS.PARTITIONS P
    JOIN SYS.TABLES T
        ON P.OBJECT_ID = T.OBJECT_ID
    JOIN SYS.ALLOCATION_UNITS AU
        ON AU.CONTAINER_ID = P.PARTITION_ID
    JOIN SYS.FILEGROUPS FG
        ON AU.DATA_SPACE_ID = FG.DATA_SPACE_ID        
WHERE
    P.INDEX_ID < 2
    AND T.NAME <> 'SYSDIAGRAMS'        
ORDER BY T.NAME

O resultado é o nome da tabela, quantidade de linhas, nome do Filegroup onde a tabela está alocada e se este Filegroup é ou não o default do banco de dados.

Curioso como sou, pensei, caramba, se a procedure sp_help retorna essa informação, de onde ela traz isso?!

EXEC sp_helptext sp_help

O trecho da procedure onde é retornada a informação do Filegroup é este:

EXEC sys.sp_objectfilegroup @objid

Fazendo um sp_helptext na procedure sys.dp_objectfilegroup chegamos finalmente à consulta realizada pelo SQL Server:

    if exists (select * from sysobjects
            where id = @objid
            and type in ('S ','U '))
        begin
            select Data_located_on_filegroup = d.name
            from sys.data_spaces d
            where d.data_space_id =
            (select i.data_space_id from sys.indexes i
             where i.object_id = @objid and i.index_id < 2)
        end

Um pouco diferente da que fiz “na unha”, deu menos voltas e chegou no mesmo resultado… Live and Learn!!!

Abraços,
Erickson

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s