Wykonanie czynności dla wielu tabel w bazie danych lub dla wielu baz danych

13-Sie-2011

Zdarza się że pewną czynność (zazwyczaj administracyjną) musisz wykonać względem wszystkich lub kilku wybranych tabel w bazie danych. Administrator bazy danych marzy wtedy o poleceniu rodem z C# np.

foreach(table in sys.tables)
{
     EXECUTE sp_spaceused table
}

Niestety tego nie ma… Można się posiłkować kursorem lub tworzyć własne procedury, albo… użyć nieudokumentowanej procedury sp_MSforeachtable !

Załóżmy, że mamy od czasu do czasu przygotować tabelę, która zawierać będzie informacje zwracane poprzez sp_spaceused. Przypomnijmy. Wykonanie polecenia:

EXECUTE sp_spaceused TestTable

spowoduje wyświetlenie informacji o tabeli TestTable zawierającej:

  • name – nazwę tej tabeli
  • rows – ilość rekordów
  • reserved – napis prezentujący informację o ilości zajmowanego przez tabelę miejsca w bazie danych
  • data – napis prezentujący informację o ilości rzeczywiście zajmowanego przez dane tabeli miejsca
  • index_size – napis prezentujący informację o ilości zajmowanego przez indeksy miejsca
  • unused – napis prezentujący informację o ilości nie używanego miejsca zajmowanego przez tę tabelę

Naszym zadaniem jest jednak uruchomienie takiego polecenia dla wszystkich tabel!

Wykorzystamy wspomnianą wcześniej sp_MSforeachtable. Upraszczając zadaniem tej procedury jest wykonanie pewnej komendy dla każdej tabeli w bazie danych.  Wystarczy zapisać komendę:

EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‚?'”

a sp_spaceused zostanie wykonane dla każdej tabeli.

Co oznacza zapis @command1? Otóż do sp_MSforeachtable można przesłać więcej parametrów:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
  • @command1 – pierwsza uruchamiana komenda
  • @replacechar – znak, którego należy używać w komendach tam, gdzie powinna się znaleźć nazwa tabeli
  • @command2 i command3 – to kolejne czynności jakie można wykonywać wobec każdej tabeli
  • @whereand – to klauzula pozwalająca ograniczyć ilość przetwarzanych tabel (z tabeli sysobjects). Klauzulę należy zacząć od AND
  • @precommand i @postcommand – komendy do uruchomienia przed przetwarzaniem wszystkich tabel i po zakończeniu tego procesu.

Znak ? jest domyślnie zastępowany nazwą tabeli ale w razie czego możesz go zmienić korzystając z parametru @replacechar.

Wróćmy jednak do naszego przykładu. Proste uruchomienie polecenia:

EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‚?'”

Pokaże wynik na ekranie. Jeżeli chciałbyś go zapisać w tabeli musisz ją najpierw utworzyć:

CREATE TABLE #RankTab
(name sysname,
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused varchar(20))

potem wykonać polecenie (jest to specyficzna forma polecenia INSERT, wpisująca do tabeli to, co zwróci EXEC):

EXEC sp_MSforeachtable @command1=”INSERT #RankTab EXEC sp_spaceused ‚?'”

wreszcie wyświetlić wynik na ekranie:

SELECT * FROM #RankTab

Oczywiście zamiast tworzyć tabelę, a potem wyświetlać jej zawartość w oddzielnych poleceniach można wszystko zlecić procedurze sp_MSforeachtable:

EXEC sp_MSforeachtable
@command1=”INSERT RankTab EXEC sp_spaceused ‚?'” ,
@precommand=’CREATE TABLE RankTab
(name sysname,
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused varchar(20))’,
@postcommand=’SELECT * FROM RankTab’

Takie polecenie jest już dość skomplikowane i na dodatek (u mnie) nie chce działać z tabelą tymczasową…

Pozostaje dodać, że mamy też bliźniaczą procedurę sp_MSforeachdb wykonującą polecenia dla każdej bazy danych.

Polecam opis na http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Komentarze:

  1. Mobilo » Blog Archive » Jak przeindeksować (przebudować) wszystkie indeksy wszystkich tabel bazy danych? napisał,

    […] Skorzystać z nieco ukrytej procedury sp_MSforeachtable, którą kokładniej opisałem tu http://www.mobilo24.eu/wykonanie-czynnosci-dla-wielu-tabel-w-bazie-danych-lub-dla-wielu-baz-danych/ a z kolei pod tym adresem znajdziesz rozwiązanie problemu indeksowania z wykorzystaniem tej […]

Dodaj komentarz:

Autor: Rafał Kraik