Início / dba / Perguntas / 16895
Accepted
mdoyle
Asked: 2012-04-24 07:03:40 +0800 CST

Referenciando o banco de dados programaticamente via T-SQL

  • 772

Estou escrevendo um procedimento armazenado que usa um nome de banco de dados como argumento e retorna uma tabela dos índices desse banco de dados e seu nível de fragmentação. Este procedimento armazenado viverá em nosso banco de dados DBA (o banco de dados que contém as tabelas que os DBAs usam para monitorar e otimizar as coisas). Os sistemas em questão são todos SQL Server 2008 R2, se isso fizer diferença.

Eu tenho a consulta básica elaborada, mas estou tentando fornecer os nomes reais dos índices. Pelo que sei, essas informações estão contidas na exibição sys.indexes de cada indivíduo. Meu problema específico é tentar fazer referência a essa exibição programaticamente do procedimento armazenado de outro banco de dados.

Para ilustrar, esta é a parte da consulta em questão:

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id] 
    AND p.index_id = b.index_id 
    AND b.index_id != 0

A consulta funciona bem quando executada a partir do banco de dados identificado por @db_id, porque está usando a exibição sys.indexes adequada. Se eu tentar chamar isso do banco de dados DBA, no entanto, tudo ficará nulo, pois a exibição sys.indexes é para o banco de dados errado.

Em termos mais gerais, preciso ser capaz de fazer algo assim:

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

ou

USE @db_name;

Tentei alternar bancos de dados ou fazer referência a outros bancos de dados usando combinações de concatenação de strings e funções OBJECT_NAME/OBJECT_ID/DB_ID e nada parece funcionar. Eu apreciaria qualquer ideia que a comunidade possa ter, mas suspeito que terei que reequipar este procedimento armazenado para residir em cada banco de dados individual.

Agradecemos antecipadamente por quaisquer sugestões.

sql-server

3 respostas

  1. Best Answer

    O SQL dinâmico é útil para esses tipos de tarefas administrativas. Aqui está um trecho de um procedimento armazenado que escrevi que não apenas obtém os níveis de desfragmentação, mas também gera o código para fazer a desfragmentação:

    select @SQL = 
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)
    • 10

  2. A alternativa ao SQL dinâmico é o SQLCMD , que pode ser chamado a partir da linha de comando, uma etapa de trabalho do agente, o cmdlet Invoke-Sqlcmd Powershell ou habilitado no SSMS . Seu exemplo na sintaxe SQLCMD seria:

    :SETVAR DatabaseName MyDatabase

SELECT * FROM $(DatabaseName).sys.indexes;

    O modo SQLCMD é um daqueles recursos que eu gostaria de ter conhecido antes. Útil em muitas situações.

    • 8

  3. Geralmente é difícil fazer referência a um conjunto de tabelas de um procedimento contido em um banco de dados diferente. Se você instalar seu procedimento no mestre, como um procedimento do sistema, ele poderá ser usado em outros contextos de banco de dados sem tentar se referir a si mesmo.

    Eu acho que: se o seu procedimento começar com 'sp_', ele se tornará universalmente visível e, se você o definir no esquema 'sys.sp_%', poderá ser usado em outros contextos de banco de dados.

    Isso forneceria uma maneira alternativa de operar em vários bancos de dados sem precisar conectar o DB_name dinamicamente.

    • 0

relate perguntas