Tuesday, June 10, 2014

sp_MSforeachdb procedure to iterate through each database

Handy SQL Server system stored procedure to iterate through each databse on a server. I used to get a list of all databases that have sysssislog table present

DECLARE @DB TABLE (DBName sysname);
INSERT INTO @DB
EXEC sp_msforeachdb 'select ''?'' from [?].sys.tables t WHERE t.name = ''sysssislog'''
SELECT * FROM @DB
WHERE DBName NOT IN ('master','tempdb','model','msdb')


The undocumented sp_MSforeachdb procedure

No comments:

Post a Comment