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

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