sp_MSforeachtable

Was ist sp_MSforeachtable?

Die gespeicherte Prozedur sp_MSforeachtable gehört zu den Systemprozeduren, die im SQL Server definiert sind. Ihr findet sie, wenn ihr euch mit dem Management Studio oder dem Data Studio mit einem Server verbindet und dann unter “System Databases” in der “master”-Datenbank unter “Programmability” in die “System Stored Procedures” schaut. Dort findet ihr einen ganzen Haufen an prozeduren. Diese sind teilweise aber nicht offiziell dokumentiert, so auch die sp_MSforeachtable, die ihr jetzt kennenlernen werdet. Ihr solltet sie daher nicht in einer produktiven Umgebung verwenden, bei der Entwicklung kann sie euch aber einiges an Arbeit abnehmen. Schauen wir sie uns also einmal an.

Wie könnt ihr sp_MSforeachtable verwenden?

Um über die Tabellen eurer Datenbank zu iterieren und für jede Tabelle ein Kommando auszuführen, könnt ihr euch selber Abfragen schreiben, die eure Tabellen in den sys-Tabellen finden, diese dann iterieren und dynamische SQL Statements zu generieren, die ihr ausführen möchtet. Oder aber ihr könnt euch auf die Vorarbeit verlassen, die Microsoft an der Stelle für euch getan hat und die sp_MSforeachtable verwenden.

Diese Prozedur hat folgende Argumente:

• @command1: das erste SQL Kommando, das ihr für jede Tabelle ausführen möchtet
• @replacechar: das Symbol das ihr in der Abfrage durch den jeweiligen Tabellennamen ersetzen möchtet. Standard hier ist das Fragezeichen, wenn ihr diesen Parameter nicht übergebt, dann wird das Fragezeichen in eurer Abfrage durch den Tabellennamen ersetzt.
• @command2 und @command3: weitere SQL Kommandos, die ihr für jede Tabelle ausführen möchtet
• @whereand: eine WHERE-Bedingung, die die Tabellen auswählt, über die ihr iteriert.
• @precommand und @postcommand: Kommandos die ihr vor oder nach dem Iterieren über die Tabellen ausführen möchtet.

Viele der Parameter werden klarer, wenn ihr anfangt, die Prozedur zu verwenden, starten wir also mit einem einfachen Beispiel auf der WideWorldImporters-Datenbank. Der folgende Befehl gibt euch alle Tabellen in der Datenbank aus:

exec sp_MSforeachtable @command1='print ''?'''

Kümmern wir uns nun um die Where-Bedingung, um die Tabellen zu filtern. Mit folgender Abfrage könnt ihr nur noch die Tabellen aus dem Application-Schema anzeigen:

exec sp_MSforeachtable 
     @command1='print ''?'''
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''

Ihr werdet später sehen, dass die sp_MSforeachtable die sysobjects-Tabelle mit der sys.all_objects-Tabelle joint, ihr könnt also alle Attribute dieser Tabellen verwenden, um eure Where-Bedingung zu schreiben. Wenn ihr tiefer in die Verwendung der sp_MSforeachtable-Prozedur einsteigen möchtet, als das in einem Ninja-Beitrag der Fall ist, solltet ihr euch diese Tabellen auf jeden Fall nochmal ansehen.

Gehen wir nun jedoch einen Schritt weiter und arbeiten mit den Tabellen, die wir hier gefiltert haben. Nun legen wir uns im dbo-Schema entgegen aller best practices eine Tabelle mit dem Namen tables an, in die wir die Tabellennamen eintragen:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'

Jetzt können wir dbo.tables abfragen:

SELECT fullname 
FROM dbo.tables

Doch wir können die Tabelle natürlich auch gleich wieder abräumen nachdem wir sie befüllt hatten, dafür verwenden wir das @postcommand:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'
    ,@postcommand = 'SELECT fullname FROM dbo.tables; DROP TABLE dbo.tables'

Als Resultat sehen wir wieder die Tabellennamen in unserem Select, wenn wir nun aber die dbo.tables-Tabelle nach dem Beenden der Prozedur abfragen möchten, stellen wir fest dass sie nicht mehr existiert:

SELECT fullname 
FROM dbo.tables

Sie wurde nämlich wie erwartet vom @postcommand wieder aufgeräumt.

Nun möchten wir vollkommen unkonventionell und in produktiven Umgebungen auch überhaupt nicht empfohlen, alle diese Tabellen neu indizieren. Dafür können wir das zweite Command verwenden, das wir an sp_MSforeachtableübergeben können:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@command2='DBCC DBREINDEX(''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'
    ,@postcommand = 'SELECT fullname FROM dbo.tables; DROP TABLE dbo.tables'

Da die Prozedur selber nicht dokumentiert ist, gibt es natürlich auch keine offizielle Dokumentation dazu. Wie könnt ihr dennoch herausfinden, was genau passieren wird, wenn ihr die Prozedur aufruft? Einfach: sucht die Prozedur im Management Studio oder im Azure Data Studio, klickt sie mit der rechten Maustaste an und klickt dann auf “Script as create”, dann seht ihr den Quellcode der Prozedur und könnt sie für euch selber analysieren, auseinandernehmen und verstehen, wie die Parameter funktionieren. Sozusagen bekommt ihr von Microsoft ein Dojo für die Verwendung von Systemtabellen frei Haus geliefert. Also Katana gezückt und stellt euch dem Feind.

Referenzen:

• Blogbeitrag mit einigen Verwendungsbeispielen