Odwołanie do bazy danych programowo za pomocą T-SQL

Piszę procedurę składowaną, która przyjmuje nazwę bazy danych jako argument i zwraca tabelę indeksów tej bazy danych oraz ich poziom fragmentacji. Ta procedura przechowywana będzie dostępna w naszej bazie danych DBA (baza danych zawierająca tabele używane przez DBA do monitorowania i optymalizacji rzeczy). Wszystkie omawiane systemy to SQL Server 2008 R2, jeśli to robi różnicę.

Opracowałem podstawowe zapytanie, ale utknąłem przy próbie podania rzeczywistych nazw indeksów. Zgodnie z moją najlepszą wiedzą informacje te są zawarte w widoku sys.indexes każdej osoby. Moim konkretnym problemem jest próba programowego odwołania się do tego widoku z procedury przechowywanej innej bazy danych.

Aby to zilustrować, jest to część spornego zapytania:

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

Zapytanie działa poprawnie po uruchomieniu z bazy danych określonej przez @db_id, ponieważ używa właściwego widoku sys.indexes. Jeśli jednak spróbuję wywołać tę funkcję z bazy danych DBA, wszystko będzie miało wartość zerową, ponieważ widok sys.indexes dotyczy niewłaściwej bazy danych.

Mówiąc bardziej ogólnie, muszę być w stanie zrobić coś takiego:

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

lub

USE @db_name;

Próbowałem przełączać bazy danych lub odwoływać się do innych baz danych, używając kombinacji konkatenacji łańcuchów i funkcji OBJECT_NAME / OBJECT_ID / DB_ID i nic nie działa. Byłbym wdzięczny za wszelkie pomysły, jakie może mieć społeczność, ale podejrzewam, że będę musiał zmienić ustawienia tej procedury składowanej, aby umieścić ją w każdej bazie danych.

Z góry dziękuję za wszelkie sugestie.

Dynamiczny SQL jest przydatny przy tego rodzaju zadaniach administracyjnych. Oto fragment procedury składowanej, którą napisałem, która nie tylko pobiera poziomy defragmentacji, ale także generuje kod do wykonania defragmentacji:

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)

Alternatywą dla dynamicznego SQL jest SQLCMD , który można wywoływać z wiersza poleceń, kroku zadania agenta, polecenia cmdlet Powershell Invoke-Sqlcmd lub włączyć w SSMS . Twój przykład w składni SQLCMD to:

:SETVAR DatabaseName MyDatabase

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

Tryb SQLCMD to jedna z tych funkcji, o których chciałbym wiedzieć wcześniej. Przydatny w wielu sytuacjach.

Zazwyczaj trudno jest odwoływać się do jednego zestawu tabel z procedury zawartej w innym DB. Jeśli zainstalujesz swoją procedurę w trybie Master, jako procedurę systemową, możesz jej użyć w innych kontekstach DB bez próby odwoływania się do siebie.

Myślę, że: jeśli twoja procedura zaczyna się od „sp_”, to staje się ona powszechnie widoczna, a jeśli zdefiniujesz ją w schemacie „sys.sp_%”, może być używana w innych kontekstach DB.

Zapewniłoby to alternatywny sposób działania w wielu bazach danych bez konieczności dynamicznego podłączania nazwy DB.