Run All .sql Files in a Directory for Deployment

• Download source - 2.7 KB

Introduction

For newer .NET application deployment, probably you don't need to do this because Microsoft database project creates Dacpac makes lives easier for automated deployment. However, life isn't always that perfect and sometimes you have to work on legacy projects that are using incremental SQL scripts (usually ordered by date). It is painful for the DBA to execute 100+ SQLs in a huge deployment, the chance of human error is high. Even worse, you cannot change the standard when the project is big and has been running for ages. Also, there are Architects and DBAs have never heard about Dacpac, believe it or not.

Background

There are ways or scripts on stackoverflow.com that does the execution SQL part, but they don't return the results for troubleshooting, especially on production we need to have a report for artifact to make sure every script executed successfully. I have written this script years ago and I have been using this for legacy projects in the past years. It just happen today that I want to share it on CodeProject to help other people.

Credits go to the following two articles:

• List all files in a folder: http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
• Concat multiple rows into single variable: http://stackoverflow.com/questions/18102283/how-to-concat-many-rows-into-one-string-in-sql-server-2008

Using the Code

The SQL script is simple, it is self explanatory and there are 3 variables you need to configure:

Set @DBServerName = 'localhost'
Set @DBName = 'YourDatabaseName'
Set @SourceFolder = 'C:\Temp\DB_SERVER\SQL\'

To use the script, you have to create a "SQL" folder and put all your .sql files there. Then, launch SQL Manager to open the DbServer_DeployScript.sql, run it. That's it.

------------------------------------------------------------------------------------
-- Execute all .SQL scripts one by one (order by filename in ascending order)
-- Written by Rini Boo 

-- Usage:  Run this in SQL Manager
-- Put all your .SQL scripts in a folder and it will run all of them automatically 
-- with Results showing if you encounter any error

-- References: 
--
-- Ref: List All files in a folder
-- http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
--
-- Ref: Concat multiple rows into single variable
-- http://stackoverflow.com/questions/18102283/how-to-concat-many-rows-into-one-string-in-sql-server-2008
	
------------------------------------------------------------------------------------

   
------------------------------------------------------------------------------------
-- Configure current user to use xp_cmdshell
------------------------------------------------------------------------------------

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

------------------------------------------------------------------------------------
-- Define the variables
------------------------------------------------------------------------------------

-- General variables 
Declare @DBServerName nVarchar(100)
Declare @DBName nVarchar(100)
Declare @SourceFolder nVarchar(250)
Declare @Command nVarchar(250)
DECLARE @CommandShellResultCode int	-- Result code is not really useful
DECLARE @CommandShellOutputTable TABLE (Line NVARCHAR(512)) -- Output is more useful for exception
DECLARE @SingleLineOutput nvarChar(max)

-- Single variables for cursor to use
DECLARE @sourceID int
DECLARE @sourceSqlFilename nvarchar(512)
DECLARE @sourceDepth int
DECLARE @sourceIsFile bit

-- Set the variables  
Set @DBServerName = 'localhost'
Set @DBName = 'YourDatabaseName'
Set @SourceFolder = 'C:\Temp\DB_SERVER\SQL\'

------------------------------------------------------------------------------------
-- Create a temp table and get the list of files from the folder
------------------------------------------------------------------------------------


-- Get all the SQL from the path
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
      DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
       ID int IDENTITY(1,1)
      ,SubDirectory nvarchar(512)
      ,Depth int
      ,IsFile bit);

INSERT #DirectoryTree (SubDirectory,Depth,IsFile)
EXEC master.sys.xp_dirtree @SourceFolder,1,1;


--SELECT * FROM #DirectoryTree
--WHERE IsFile = 1 AND RIGHT(SubDirectory,4) = '.sql'
--ORDER BY subdirectory asc


------------------------------------------------------------------------------------
-- Exec one by one 
------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#ResultSummary') IS NOT NULL
      DROP TABLE #ResultSummary;

-- Create a result table for reference after sql being executed
CREATE TABLE #ResultSummary (
	   SqlFilename nvarchar(100),
	   ResultCode nvarchar(100),  -- for xp_cmdshell
	   ResultOutput nvarchar(max)   -- more for sqlcmd exception 
		);

-- Loop thro each SQL using cursor

DECLARE ScriptCursor CURSOR FOR
 SELECT * FROM #DirectoryTree
 WHERE Isfile = 1 AND RIGHT(SubDirectory,4) = '.sql'
 ORDER BY SubDirectory asc;
 
OPEN ScriptCursor;
FETCH NEXT FROM ScriptCursor
INTO @sourceID, @sourceSqlFilename, @sourceDepth, @sourceIsFile
 
-- Note: first one already been fetched
        
WHILE @@FETCH_STATUS = 0
   BEGIN
		-- PRINT 'Debug' + @sourceSqlFilename
     
		-- Clean up output table
        Delete from @CommandShellOutputTable
     
		-- Construct the command and execute the query           
		Set @Command = 'sqlcmd -S ' + @DBServerName + ' -d  ' + 
		_@DBName + ' -i ' + '"' +@SourceFolder + @sourceSqlFilename + '"'   
		print @Command
		
		-- Get the output and execute the command (with multiple lines output)
		INSERT INTO @CommandShellOutputTable
		EXEC @CommandShellResultCode= xp_cmdshell  @Command 
		
		
		---- Combine multiple lines output into single variable
		SELECT @SingleLineOutput = (SELECT STUFF((
		SELECT ',' + line
		FROM  @CommandShellOutputTable
		FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''))
    
    
	
		-- Write the 2 result variables into a temp table for summary		 
		 IF (@CommandShellResultCode = 0)
			   Insert INTO #ResultSummary(SqlFilename, ResultCode, ResultOutput)
				Values (@sourceSqlFilename, 'xp_cmdshell Executed', @SingleLineOutput)
		   ELSE
			 Insert INTO #ResultSummary(SqlFilename, ResultCode, ResultOutput) 
				Values (@sourceSqlFilename, 'xp_cmdshell Failed', @SingleLineOutput)     
     
      FETCH NEXT FROM ScriptCursor
      INTO @sourceID, @sourceSqlFilename, @sourceDepth, @sourceIsFile    
   END;
   
CLOSE ScriptCursor;
DEALLOCATE ScriptCursor;

-- Show results
Select SqlFilename, ResultCode, ISNULL(ResultOutput, 'SUCCESSFUL') _
as SqlcmdResult from #ResultSummary order by SqlFilename asc 

GO

As I have another script DbServer_BackupScript.sql that does that backup, based on current time stamp. This is handy and very simple. Just to throw it in here in case you may want it. You need to modify the backup path and "YourDatabaseName" in the script:

-- Backup database 

Declare @filename Varchar(100)
Declare @datetime Varchar(50)

Set @datetime = CONVERT(char(50), GetDate(),121)
Set @filename = (SELECT LTRIM(Replace(@datetime, ':','-')))
Set @filename = (SELECT RTRIM(Replace(@filename, '.','-')))
Set @filename = (SELECT RTRIM(Replace(@filename, ' ','_')))

Set @filename = 'C:\Backup\YourDatabaseName_' + @filename + '.bak' 
select @filename

USE [YourDatabaseName];
BACKUP DATABASE [YourDatabaseName] 
TO DISK = @filename

Script in Action

In this screenshot below, I have 3 SQL scripts with 1 stored procedure, 2 update scripts with one valid + one invalid. As you can see, you can easily see which one fails.

Hope you find this useful.

History

• 2015-03-05 - First published