SQL Server Script Execution Order Explained

sql serversql-server-2016

I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below:

Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many .UserDefinedFunction.sql files.

I'm going to make a batch script that goes to each folder and runs sqlcmd on every file in that folder.

Does the execution order matter here, and do I have the right order?

Best Answer

The execution order absolutely matters, and you shouldn't change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can't find the View it references. This is why generating scripts puts them in order by dependency instead of object type.

Only in the very rare case where all your scripts don't have dependencies on each other (or your re-ordering is coincendentally in order of dependency still), then you'd be ok.

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

This one was quite tricky. I didn't figure out how to access DBCC messages, but am working on it. Anyway, this Powershell script attaches databases, executes DBCC CHECKDB and detaches dbs.

# Get database files from where ever they are
$databases = gci "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data2\*.mdf"
$sqlCmds = @()
# Parse db names from file names somehow and generate attach/check/detach statements
foreach($db in $databases) {
  $dbName = [regex]::Replace($db.Name, "(?i)\.mdf", "")
  $lgName = [regex]::Replace($db.FullName, "(?i)\.mdf", ".ldf")
  $sqlCmds += $("CREATE DATABASE {0} ON (FILENAME = '{1}') LOG ON (FILENAME = '{2}') FOR ATTACH;"  -f $dbName, $db.FullName, $lgName)
  $sqlCmds += $("DBCC CHECKDB ({0});" -f $dbName)
  $sqlCmds += $("EXEC sp_detach_db @dbname='{0}';" -f $dbName)
}

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Server=SERVER\INSTANCE;Database=master;Integrated Security=True"
$s = $sqlCmds[0]

# Try and execute the statements   
try {
    foreach($s in $sqlCmds) {
      $ret = $conn.ExecuteNonQuery($s)
      $ret
    }
} catch [system.exception] {
  $_.Exception.ToString()
}

Sql-server – How are foreign keys resolved when creating tables in batch

the batch will fail.

do all the table creates first, then do all the foreign key constraints AFTER all the tables are created.

create the tables with constraints in the correct ORDER so that constraints don't fail.

Best Answer

Related Solutions

Sql-server – attach database – verify – detach from a script or a batch file

Sql-server – How are foreign keys resolved when creating tables in batch

Related Question