Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases

This post is a continuation of my pervious articles:

• Introduction
• Extracting Attachments from Selected Emails

In the last part, I showed how to extract attachments from the emails. In this part, we will see how to execute them in a batch on a number of SQL Server databases.

The Problem

To execute a batch of *.sql files on one or more SQL Server databases uninterrupted, without any human intervention. The sql files are assumed to be named in ascending order of their execution sequence.

The Solution

1. Open SQL Server Management Studio.
2. Copy and paste the following code in a new SQL file.
   

   
   USE Master
   GO
   
   -- To allow xp_cmdshell execution. 
   PRINT 'Enabling xp_cmdshell utility...'
   EXEC sp_configure 'show advanced options', 1
   RECONFIGURE
   EXEC sp_configure 'xp_cmdshell', 1
   RECONFIGURE
   GO
   
   DECLARE @ScriptFilesDir VARCHAR(MAX), @Databases VARCHAR(MAX), @ServerName VARCHAR(MAX)
   
   -- ******************************************************************************************
   -- ***************************** USER CONFIGURABLE VARIABLES ********************************
   -- ******************************************************************************************
   SET @ScriptFilesDir		= 'C:\Temp\Scripts\'              --'
   SET @ServerName			= 'Your_SQLServer_Name'
   SET @Databases			= 'Database1, Database2, Database3, Database4, Database5, Database6'
   -- ******************************************************************************************
   
   DECLARE @FileList Table (FileNumber INT IDENTITY(1,1), FileName VARCHAR(255), Command VARCHAR(MAX))
   DECLARE @DatabaseList Table (DBNumber INT IDENTITY(1,1), DBName VARCHAR(MAX))
   DECLARE @Command VARCHAR(2048), @Command1 VARCHAR(2048) 
   
   SET NOCOUNT ON
   SET @Command = 'ECHO Running Scripts Started %DATE% %TIME% > ' + @ScriptFilesDir + 'Output.txt'
   EXEC xp_cmdshell @Command, NO_OUTPUT
   
   -- Get a list of *.sql files we want to execute on our databases 
   SET @Command = 'DIR /B /O:-N ' + @ScriptFilesDir + '*.sql'
   INSERT INTO @FileList (FileName) EXEC xp_cmdshell @Command 
   DELETE FROM @FileList WHERE FileName IS NULL OR FileName NOT LIKE '%.sql'
   UPDATE @FileList SET Command = 'sqlcmd -S ' + @ServerName 
   							 + ' -d %DATABASE% -i "' + @ScriptFilesDir + FileName 
   							 + '" >> ' + @ScriptFilesDir + 'Output.txt'
   
   -- Create a list of databases to work upon 
   DECLARE @BeginPos INT, @NextPos INT, @SubStrLen INT
   SELECT @BeginPos = 0, @NextPos = 1
   WHILE @NextPos > 0
   BEGIN
   	SET @NextPos = CHARINDEX(',', @Databases, @BeginPos + 1)
   	SET @SubStrLen = CASE WHEN @NextPos > 0 THEN @NextPos ELSE LEN(@Databases) + 1 END - @BeginPos - 1
   	INSERT INTO @DatabaseList (DBName) VALUES (LTRIM(RTRIM((SUBSTRING(@Databases , @BeginPos + 1, @SubStrLen)))))
   	SET @BeginPos = @NextPos
   END
   
   -- Run the script files one by one on each of the specified databases 
   PRINT ''
   PRINT 'Running Scripts...'
   DECLARE @DBName VARCHAR(255), @DbNumber INT, @FileName VARCHAR(255), @FileNumber INT
   SELECT @FileNumber = MAX(FileNumber) FROM @FileList 
   WHILE (@FileNumber > 0)
   BEGIN
   	SELECT @FileNumber = @FileNumber - 1, @FileName = FileName, @Command = Command FROM @FileList WHERE FileNumber = @FileNumber 
   	RAISERROR ('    Executing file %s',0, 1, @FileName) WITH NOWAIT
   	SET @Command1 = 'ECHO ' + @FileName + ': >> ' + @ScriptFilesDir + 'Output.txt'
   	EXEC xp_cmdshell @Command1, NO_OUTPUT
   
   	SELECT @DbNumber = MAX(DbNumber) FROM @DatabaseList
   	WHILE @DbNumber > 0 
   	BEGIN
   		SELECT @DbNumber = @DbNumber - 1, @DBName = DBName FROM @DatabaseList WHERE DbNumber = @DbNumber
   		--RAISERROR ('        %s',0, 1, @DBName) WITH NOWAIT
   		SET @Command1 = REPLACE(@Command, '%DATABASE%', @DBName)
   		EXEC xp_cmdshell @Command1, NO_OUTPUT
   	END
   END
   PRINT 'Running Scripts Complete'
   PRINT ''
   
   -- Show the program output & errors on the screen. 
   DECLARE @OutputTable Table (Output VARCHAR(MAX))
   SET @Command = 'type "' + @ScriptFilesDir + 'Output.txt"'
   INSERT INTO @OutputTable EXEC xp_cmdshell @Command 
   SELECT * FROM @OutputTable WHERE Output IS NOT NULL
   SET @Command = 'ECHO Running Scripts Completed %DATE% %TIME% >> ' + @ScriptFilesDir + 'Output.txt'
   EXEC xp_cmdshell @Command, NO_OUTPUT
   GO
   
   -- To disable xp_cmdshell again once we are thru with our work. 
   PRINT 'Disabling xp_cmdshell utility...'
   EXEC sp_configure 'xp_cmdshell', 0
   RECONFIGURE
   EXEC sp_configure 'show advanced options', 0
   RECONFIGURE
   GO
   
   PRINT ''
   PRINT 'Check the Output.txt file for intermediate output and errors.'
   GO
   
   
   

3. Modify the code and set the variables appropriately.
   
   @ScriptFilesDir: The folder path where your script files are located.

   @ServerName: The name of your SQL Server instance.

   @Databases: A comma separated list of databases, on which the scripts should run.

4. Save this file somewhere on your disk (for future use).
5. Press F5 key or click the Execute button to run the script.
6. Switch over to the Messages tab to see the current status of the script run. Wait for the script to finish execution.
7. Once the script has finished execution, you can view the result of execution and any errors in the Output.txt file. This is created in the @ScriptFilesDir folder

Enjoy! 🙂

Further Developments

This script was meant for my personal use. So I didn’t care much about packaging it nicely/properly, maybe into a Stored Procedure etc. I’m comfortable opening this SQL script file everytime I need it, and click the Execute button.

However if you want to implement the solution on a professional level (e.g. within your organization etc.), then you may consider modifying the script and converting it into Stored Procedures etc. or develop a front-end application for this.

 

Previous Parts:

1. Introduction
2. Extracting Attachments from Selected Emails