SQL-statements uitvoeren op alle tabellen en databases

In SQL wil je soms een statement uitvoeren in alle databases. Of je wil een statement uitvoeren voor alle tabellen in één database of in alle databases. SQL heeft daar twee handige procedures voor: sp_MSforeachdb en sp_MSforeachtable.

sp_MSforeachdb

De aanroep van sp_MSforeachdb is eenvoudig, zie:

DECLARE @sql nvarchar(max) = 'print ''Database: ?''';
EXECUTE sp_MSforeachdb @sql;

Je krijgt dan iets te zien als:

Database: master
Database: tempdb
Database: model
Database: msdb
Database: database.naam
Database: mijn-database

Wil je een statement uitvoeren per database, dan kan je die database actief maken via USE <Databasenaam>. De procedure sp_MSforeachdb geeft de naam van de database mee in de variabele ? zoals je hierboven kan zien.

Voeren we dit uit, dan krijgen we nu echter te zien:

DECLARE @sql nvarchar(max) = '
       USE ?
       print ''Database: ?''
       select * from sys.tables
';

EXECUTE sp_MSforeachdb @sql;

Resultaat:

Database: master
Database: tempdb
Database: model
Database: msdb
Msg 911, Level 16, State 1, Line 2
Database database does not exist. Make sure that the name is entered correctly.

Dat is omdat ‘USE database.naam’ niet de juiste aanroep is als er een puntje in de databasenaam zit, maar USE [database.naam] wel. Het resultaat is dan:

DECLARE @sql nvarchar(max) = '
       USE [?]
       print ''Database: [?]''
       select * from sys.tables
';

EXECUTE sp_MSforeachdb @sql;

Geeft:

Database: [master]
Database: [tempdb]
Database: [model]
Database: [msdb]
Database: [database.naam]
Database: [mijn-database]

Nu krijg je bij de query resultaten per database de tabellen te zien in een overzicht.

Echter, sp_MSforeachdb is een ongedocumenteerde en niet-ondersteunde procedure. Het werkt, maar je kan overwegen om de databases vanuit sys.databases op te halen, zie: https://docs.microsoft.com/en-us/answers/questions/277805/execute-the-query-in-one-server-with-different-dat.html.

SP_MSforeachtable

SP_MSforeachtable werkt op vergelijkbare wijze:

DECLARE @sql nvarchar(max) = '
print ''?''
SELECT * FROM ?
';

EXECUTE sp_MSforeachtable @sql;

Bovenstaande geeft alle resultaten terug per tabel in de actieve database. De databasenaam in ?  is opgebouwd als [schema].[naam] en is dus al veilig opgemaakt. In dit geval zou [?] leiden tot een naam als [[dbo].[teksten]] en die tabel bestaat niet.

Sp_MSforeachtable kan je gebruiken om van een alle tabellen alle indexes opnieuw te builden, zie bijvoorbeeld:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Zie https://www.sqlservercentral.com/blogs/common-uses-of-stored-procedure-sp_msforeachtable voor meer voorbeelden.