Executing a Batch of SQL Scripts

Overview

Let's say you have a lot of SQL Server scripts that you maintain each of which has its own purpose. For a simple scenario, let's say you have a different script for each database object that you create, one script for creating tables, another for creating views, another for creating stored procedures and yet another for creating functions.

C:\SQL Scripts> dir/b

Create Functions.sql
Create Stored Procedures.sql
Create Tables.sql
Create Views.sql

Executing these scripts can easily be done manually since you only have 4 scripts to open and execute using Query Analyzer. What if as the design of your database gets more complex, you create a separate script for each stored procedure instead of having all of them in just one script. There should be an easy way to execute these batch of scripts without manually opening them one at a time and executing them using Query Analyzer.

Here's a script that you can use that will execute all SQL scripts in a particular directory. In this particular case, the scripts are located in the C:\SQL Scripts directory and they have a file extension of .sql.

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\SQL Scripts\*.sql"'
GO

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @vFileName            VARCHAR(200)
DECLARE @vSQLStmt             VARCHAR(4000)

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- The following SET command must be on a single line or else an error will be generated.
    -- It is split in this script for readability purposes.
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S Server Name -U User Name -P Password
                     -d Database Name -i "C:\SQL Scripts\' + @vFileName + '"'''
    EXECUTE (@vSQLStmt)

    FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

Description

To get the list of SQL scripts from a particular directory, the script made use of the master.dbo.xp_cmdshell system stored procedure. The master.dbo.xp_cmdshell system stored procedure executes a given command string as an operating-system command shell and returns any output as rows of text. Execute permissions for the xp_cmdshell system stored procedure default to members of the sysadmin fixed server role but can be granted to other users.

In this script, the operating-system command being executed is the dir command, which displays a list of files and subdirectories in a directory. The /b argument is passed so that the output of the dir command will use a bare format with no heading information or summary. The list of SQL scripts are then inserted into a global temporary table (##SQLFiles).

The next part of the script goes through each file in the global temporary table using a cursor then executes each SQL script. Execution of each script is done with the use of the xp_cmdshell system stored procedure together with osql command prompt utility. The osql utility allows you to enter Transact-SQL statements, system procedures and script files.