[RESOLVED] SQLCMD to run all *.sql files in a folder

Hi friends,

Does anyone know how to run all the sql files in a folder without specifying the filenames hardcoded using SQLCMD?

This is what I have till now.

Code:

---

PRINT 'Running Scripts...'

:On Error exit

:r c:\Scripts\01_CREATE_TABLES.sql
:r c:\Scripts\02_TABLE_INSERTS.sql
:r c:\Scripts\03_CREATE_INDEXES.sql
:r c:\Scripts\04_CREATE_PROCEDURES.sql
:r c:\Scripts\04_CUSTOM_CHANGES.sql
:r c:\Scripts\05_CUSTOM_CHANGES.sql
:r c:\Scripts\06_CUSTOM_CHANGES.sql

PRINT 'Running Scripts Complete'
GO

---

The filenames are hardcoded here, which I want to replace to get dynamically by querying the filesystem. The files to be executed should be sorted in ascending order.

I need to feed it something like the result of this dos command:

Code:

---

DIR C:\Scripts\*.sql /B /ON

---

I've never done it but the question intrigued me so I did a bit of googling and found this.

The idea is to use the xp_cmdshell system sproc to run the windows Dir command. The important bit of code is this:-

Code:

---

SELECT @cmd = 'Dir "' + @path + '"'

INSERT INTO Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd

---

Thanks FunkyDexter. That gave me a good direction to start.

Here is what I came up with for anyone looking for a similar solution.

Code:

---


SET NOCOUNT ON
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 @FileList Table (FileNumber int identity(1,1), FileName varchar(255), Command varchar(2048))
DECLARE @OutputTable Table (Output varchar(MAX))
DECLARE @BaseDir varchar(2048)
DECLARE @FileName varchar(255)
DECLARE @Command varchar(2048)
DECLARE @FileNum int

SET @BaseDir = 'C:\Temp\Scripts\'
SET @Command = 'ECHO Running Scripts Started %DATE% %TIME% > ' + @BaseDir + 'Output.txt'
EXEC xp_cmdshell @Command, NO_OUTPUT
SET @Command = 'DIR /B /O:-N ' + @BaseDir + '*.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 -d MyDB -i "' + @BaseDir + FileName + '" >> ' + @BaseDir + 'Output.txt'

PRINT ''
PRINT 'Running Scripts...'
SELECT @FileNum = MAX(FileNumber) FROM @FileList
-- SELECT * FROM @FileList
WHILE (@FileNum > 0)
BEGIN
        SELECT @FileName = FileName, @Command = Command FROM @FileList WHERE FileNumber = @FileNum
        RAISERROR ('    Executing file %s',0, 0, @FileName) WITH NOWAIT
        SET @FileName = 'ECHO ' + @FileName + ': >> ' + @BaseDir + 'Output.txt'
        EXEC xp_cmdshell @FileName, NO_OUTPUT
        EXEC xp_cmdshell @Command, NO_OUTPUT
    SET @FileNum = @FileNum - 1
END
PRINT 'Running Scripts Complete'
PRINT ''

SET @Command = 'type "' + @BaseDir + 'Output.txt"'
INSERT INTO @OutputTable EXEC xp_cmdshell @Command
SELECT * FROM @OutputTable WHERE Output IS NOT NULL
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

---

This might be a very crude way of doing it, but its workable for me. If anyone has a better solution please post here.

EDIT: Added "-d MyDB" flag to the sqlcmd statement. Without this the queries are executed on Master database.