T-SQL Tips and Tricks: Executing a Query in multiple databases

If you are a Full-stack Developer, SQL Database Developer or SQL Database Administrator and if you are working with multiple databases, then this query will be useful in following situations.

Verifying Schema 
- You want to check if all databases have a certain table 
- You want to check if all databases have certain index or columns or stored procedures 

Verifying Data
- Check if all databases have particular lookup data 
- Check if all databases have certain valid data in certain tables 

A simple approach is to write the query and then run it in each database one by one. Our database team maintains more than dozen databases (same schema, different clients) in different environments - development, QA, Pre-production and Production. Following is a very useful script to execute a query in multiple database and verify the output. 

Use "Select db_name()" in your queries while running in multiple databases, that way you know which database the output belongs to. 

/*
sharpquest.exec_in_all_db
	- runs a given query in all databases whose name starts with the given prefix 
Example:
	exec sharpquest.exec_in_all_db 'qa_', 'select top 10 db_name(), * from dbo.Customer'
	-- will execute the select query in all databases whose name start with 'qa_'
*/
CREATE     procedure sharpquest.exec_in_all_db(  
 @dbName varchar(100) = 'qa_',  
 @SQL nvarchar(max)   
)  
as  
BEGIN  
 DECLARE cur_dbname CURSOR FOR  
  SELECT [name] FROM sys.databases where [name] like @dbName + '%'  
  
 DECLARE @currentName varchar(100)  
 DECLARE @curSQL nvarchar(max)   
  
 OPEN cur_dbname   
 FETCH NEXT FROM cur_dbname INTO @currentName   
  
 SET @SQL = 'EXEC(''' + REPLACE(@SQL, '''', '''''') + ''')'  
  
 BEGIN TRY   
  WHILE @@FETCH_STATUS = 0   
  BEGIN  
   SET @curSQL = ';use ' + @currentName + ';'   
   PRINT @curSQL;  
   SET @curSQL = @curSQL  + '; ' + CHAR(10) + @SQL;  
   exec sp_executesql @curSQL   
   FETCH NEXT FROM cur_dbname INTO @currentName   
  END   
 END TRY   
 BEGIN CATCH   
  PRINT '-- ERROR ---' + ERROR_MESSAGE()  
  PRINT '-- SQL -- '   
  PRINT @curSQL   
 END CATCH   
   
 CLOSE cur_dbname   
 DEALLOCATE cur_dbname   
END