SQL Server Database Iterator
February 14, 2012
Leave a comment
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
Categories: Database, SQL Server, T-SQL
Database, InformationSchema, Iteration, SQL Server
