Dynamically read files in VS DBPro (using SQLCMD)

Question

I'm using VS2008 DBPro. In my PostDeploy file I have many lines like this one

:r .\Data\Test\Classifiers.CodeType.data.sql
:r .\Data\Test\Classifiers.Currency.data.sql
:r .\Data\Test\Classifiers.LOB.data.sql

What I would like is to create a variable ProjectName so I could easely deploy different project data. Something like this (doesn't work)

:setvar ProjectName "Test"

:r .\Data\$(ProjectName)\Classifiers.CodeType.data.sql
:r .\Data\$(ProjectName)\Classifiers.Currency.data.sql
:r .\Data\$(ProjectName)\Classifiers.LOB.data.sql

It would be even better If I could read ALL files in the folder without specifying a path to it.

Answer 1

I have found out how this could be done.

First you need to enable xp_cmdshell utility

RAISERROR ('Enabling xp_cmdshell utility...', 0, 1) WITH NOWAIT
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

Next you have to define a stored procedure which will do all the work. It works by reading all files into a temporary table and then runs SQLCMD command to parse each of found *.sql files

CREATE PROCEDURE [Builder].[RunScriptsInFolder]
    @scriptsDir varchar(255)
AS

IF len(@scriptsDir) = 0
    RETURN 0

DECLARE @Message VARCHAR(254)
SET @Message = 'Loading files in ' + @scriptsDir + ' directory...'
RAISERROR (@Message, 0, 1) WITH NOWAIT 

DECLARE @FileList Table (FileNumber int identity(1,1), FileName varchar(255), Command varchar(2048))
DECLARE @OutputTable Table (Output varchar(MAX))
DECLARE @FileName varchar(255)
DECLARE @Command varchar(2048) 
DECLARE @FileNum int
DECLARE @databaseName varchar(255)

SET @databaseName = db_name()

SET @Command = 'DIR /B /O:-N ' + @scriptsDir + '*.sql'
INSERT INTO @FileList (FileName) EXEC xp_cmdshell @Command 
UPDATE @FileList SET Command = 'sqlcmd -d ' + @databaseName + ' -i "' + @scriptsDir + FileName + '"'

WHILE EXISTS(SELECT * FROM @FileList)
BEGIN
    SELECT TOP(1) @FileNum = FileNumber, @FileName = FileName, @Command = Command FROM @FileList 

    SET @FileName = '  :r ' + @FileName
    RAISERROR (@FileName, 0, 1) WITH NOWAIT 
    EXEC xp_cmdshell @Command

    DELETE FROM @FileList WHERE FileNumber = @FileNum
END
RETURN 0;

All you have to do now is call this stored procedure (You need to pass full path to folder containing SQL files. You can get path to your project from MSBuild properties). Also note that I've added few lines around the call to RunScriptsInFolder call. Because you won't know in which order files in your folder are executed you should disable all foreign keys checks before doing it and enable them once you finished

RAISERROR ('Disabling all constraints...', 0, 1) WITH NOWAIT 
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

---- Run all files specified folder
BEGIN TRANSACTION
EXEC [Builder].[RunScriptsInFolder] '$(ProjectDir)Scripts\Post-Deployment\Data\'
COMMIT TRANSACTION

---- Enable all constraints
RAISERROR ('Re-enabling all constraints...', 0, 1) WITH NOWAIT 
exec sp_msforeachtable @command1="print '?'", @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

You may also wonder why $(ProjectDir) variable doesn't work for you. To enable it open your *.dbproj file with text editor and add this code at the end.

<PropertyGroup>
  <SetVariables>
    <Variable Name="ProjectDir" Value="$(ProjectDir)" />
  </SetVariables>
</PropertyGroup>

Alternatively you can open your DB project properties, find tab Variables and add set variable ProjectDir=$(ProjectDir)