|
|
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.
|