How to execute a script on multiple databases?

This blog post will describe how you can execute a script on multiple databases. For instances to retrieve the number of records in a table or to retrieve a specific value. The concept of the script is to generate the script which you want to execute on all database. In the example I will use, the script will retrieve the release of the software on the databases of Exact. If it is not an Exact database the script will not be executed.

-- Begin script: How to execute a script in multiple databases?

SET NOCOUNT ON

IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects
WHERE Xtype ='U' AND ID = object_id(N'tempdb..#TableExport'))
BEGIN
DROP TABLE #TableExport
END
-- Create table to store retrieved data.
CREATE TABLE #TableExport (DBNAME NVARCHAR(256),Value nvarchar(3))

DECLARE DB CURSOR FOR SELECT DBID,NAME FROM MASTER..SYSDATABASES WHERE DBID > 4 -- Select Database ID and Name of all User databases
OPEN DB
DECLARE @DBNAME varchar(256); -- Database name
DECLARE @DBID nvarchar(3); -- Database ID
DECLARE @SQL nvarchar(4000); -- Generated SQL Script which will be executed against all databases

FETCH NEXT FROM DB INTO @DBID,@DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
-- Generate SQL script for the first database in paramater @SQL
SET @SQL = N'USE [' + @DBNAME + ']IF OBJECT_ID(''ExactLicense'') IS NOT NULL'
+ ' INSERT INTO #TableExport (DBNAME,Value)'
+ ' SELECT '''+@DBNAME + ''' as dbname,B.longvalue
FROM ['+@DBNAME+']..BacoSettings B
WHERE SettingType = 0 AND SettingGroup = ''General'' AND Settingname = ''Batch''
'
EXEC SP_EXECUTESQL @SQL -- Execute the generated script.

FETCH NEXT FROM DB INTO @DBID,@DBNAME -- Generate and execute script for the second database
END
CLOSE DB
DEALLOCATE DB
SELECT * FROM #TableExport ORDER BY DBNAME --Retrieve all data.
DROP TABLE #TableExport

-- END script: How to execute a script in multiple databases?