SQL Server Database Iterator

 

I had an issue the other day where I needed to inspect each database on a server.  I wrote this little snippet to do so. It gets a list of databases from the server (minus the first 4 system databases), then inspects them to see if a table exists, then queries that table if it exists. 

 

Feel free to use it / modify it however you wish.  If you have better ways of doing it – post them too. 

SET NOCOUNT ON
GO
use master
go

Declare 
@dbname nvarchar(500),
@variable1 int,
@variable2 int,
@variable3 int,
@totaldb int = 0,
@totaldbonserver int = 0,
@totaldbwithmatches int = 0

-- Get non system databases
Declare mycursor CURSOR for select name, database_id from SYS.databases where database_id > 4 order by name desc

open mycursor

fetch next from mycursor into @dbname, @variable1

while (@@FETCH_STATUS <> -1)
    BEGIN        
        DECLARE @ParmDefinition NVARCHAR(500)
        Declare @mysql nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].INFORMATION_SCHEMA.TABLES where Upper(TABLE_NAME) like ''MyTable''';
        SET @ParmDefinition = N'@variable2OUT int OUTPUT'
        set @totaldbonserver = @totaldbonserver + 1
        Execute sp_executesql @mysql, @ParmDefinition, @variable2 OUTPUT            
        
        if @variable2 = 1
        BEGIN
            DECLARE @ParmDefinition2 NVARCHAR(500)
            Declare @mysql2 nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].dbo.MyTable';
            SET @ParmDefinition2 = N'@variable2OUT int OUTPUT'
            Execute sp_executesql @mysql2, @ParmDefinition2, @variable3 OUTPUT                
            set @totaldb = @totaldb + 1
            
            if @variable3 > 1
            BEGIN
                Print @dbname + ' matched the criteria'            
                set @totaldbwithmatches = @totaldbwithmatches + 1
            END
            ELSE
            Select 1    
        END    
        
        fetch next from mycursor into @dbname, @variable1
    END
    PRINT 'Total databases on server: '
    Print @totaldbonserver
    
    PRINT 'Total databases tested () : '
    Print @totaldb
    
    PRINT 'Total databases with matches: '
    Print @totaldbwithmatches
CLOSE mycursor
DEALLOCATE mycursor