Ausführen von SQL Server-Skripts

Schreiben Sie ein Windows-Skript, verwenden Sie die FÜR - Konstrukt in einer Schleife durch Ihre Dateien und verwenden Sie die SQLCMD - Dienstprogramm ausführen jeder Datei.

for %f in (c:\MySQLScripts\*.sql) do sqlcmd -i %f

Ich habe das folgende script, für eins meiner Projekte:

SET NOCOUNT ON

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

--Create the Folder- en filetables.
DECLARE @SQLFolders TABLE ( SQLFolderName VARCHAR(MAX))
DECLARE @SQLFiles TABLE ( SQLFileName VARCHAR(MAX))
DECLARE @MainFolder VARCHAR(MAX)
DECLARE @FileName VARCHAR(MAX)
DECLARE @FolderName VARCHAR(MAX)
DECLARE @SQLStatement VARCHAR(2000)

SET @MainFolder = 'C:\ProjectName\'

--Fill the foldertable and loop through them.
INSERT INTO @SQLFolders VALUES ('CreateScripts\')
INSERT INTO @SQLFolders VALUES ('ChangeScripts\')

DECLARE cFolders CURSOR LOCAL FOR
    SELECT [SQLFolderName]
    FROM @SQLFolders
OPEN cFolders
FETCH NEXT FROM cFolders INTO @FolderName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Fill the file-table and loop through.
    SET @SQLStatement = 'dir /b "' + @MainFolder + @FolderName + '*.sql"'
    INSERT INTO @SQLFiles
    EXECUTE master.dbo.xp_cmdshell @SQLStatement

    DECLARE cFiles CURSOR LOCAL FOR
        SELECT DISTINCT [SQLFileName]
        FROM @SQLFiles
        WHERE [SQLFileName] IS NOT NULL AND
              [SQLFileName] != 'NULL' AND
              [SQLFileName] != 'File Not Found'
        ORDER BY [SQLFileName]
    OPEN cFiles
    FETCH NEXT FROM cFiles INTO @FileName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQLStatement = 'SQLCMD -d hantisdb -i' +  @MainFolder + @FolderName + @FileName
        EXECUTE master.dbo.xp_cmdshell @SQLStatement

        FETCH NEXT FROM cFiles INTO @FileName
    END

    DELETE FROM @SQLFiles

    CLOSE cFiles
    DEALLOCATE cFiles
    FETCH NEXT FROM cFolders INTO @FolderName
END

CLOSE cFolders
DEALLOCATE cFolders
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE

SET NOCOUNT OFF

Ich diese neu erstellen meiner Datenbank und starten mit einer frischen Datenbank jeden Tag.
Beachten Sie, dass es ausgeführt wird, die Dateien in alfabetical bestellen!