When your building a standard SQL Server configuration or have some deployment scripts that need to be run from a folder. you properly dont want to do this manually one after the other. With that i have wrote the below scripts, it’s actually pretty, especially in PowerShell, but if you don’t have access to PowerShell, i have also wrote a T-SQL version which uses xp_cmdshell and SQLCMD.

PowerShell Script to Run All *.SQL files in Folder

IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }
 
 
$localScriptRoot = "c:\sql\Scripts"
$Server = "localhost"
$scripts = Get-ChildItem $localScriptRoot | Where-Object {$_.Extension -eq ".sql"}
 
foreach ($s in $scripts)
    {
        Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen -ForegroundColor White
        $script = $s.FullName
        Invoke-Sqlcmd -ServerInstance $Server -InputFile $script
    }

T-SQL Script to Run All *.SQL files in Folder

/*

Query - Run All *.sql Scripts in Folder

*/

-- Server to run script
DECLARE @Server VARCHAR(255);
SET @Server = 'REXGBASQLP024';

-- Define location of folder with scripts to run (Always have \ at the end)
DECLARE @Folder VARCHAR(255);
SET @Folder = 'C:\_Share\Build\';

-- Define DIR command to find scripts in @Folder
DECLARE @DirCmd VARCHAR(1000);
SET @DirCmd = 'dir /b ' + @Folder + '*.sql';

-- Create Temp table to list scripts to run
CREATE TABLE #SQLScripts
    (
      ScriptName VARCHAR(2000)
    );

INSERT  INTO #SQLScripts
        EXECUTE xp_cmdshell @DirCmd;

-- Declare var for calling scripts
DECLARE @SqlCmd VARCHAR(1000);
-- Declare script var name
DECLARE @script NVARCHAR(128);
-- Define the cursor
DECLARE ScriptCursor CURSOR
 
-- Define the cursor dataset
FOR
    SELECT DISTINCT
            [ScriptName]
    FROM    #SQLScripts
    WHERE   [ScriptName] IS NOT NULL
            AND [ScriptName] <> 'NULL'
            AND [ScriptName] <> 'File Not Found'
    ORDER BY [ScriptName];


-- Start loop
OPEN ScriptCursor;
 
-- Get information from the first row
FETCH NEXT FROM ScriptCursor INTO @script;
 
-- Loop until there are no more rows
WHILE @@fetch_status = 0
    BEGIN
        PRINT 'RUNNING SCRIPT : ' + @script;
        SET @SqlCmd = 'EXEC xp_cmdshell "sqlcmd -s ' + @Server + ' -i '
            + @Folder + @script + '"';
        PRINT @SqlCmd;
        EXEC(@SqlCmd);
-- Get information from next row
        FETCH NEXT FROM ScriptCursor INTO @script;
    END;
 
-- End loop and clean up
CLOSE ScriptCursor;
DEALLOCATE ScriptCursor;
DROP TABLE #SQLScripts; 
GO

Advertisements