sql server - How to script SQL object (tables, procs) deployments to avoid errors due to inter-dependencies?

Question

I have a fairly large amount of sql server objects that I would like to batch deploy. Each object (table, view, procedure, etc) is in it's own file.

The problem is, many of the objects have interdependencies, so the order of creation is important, otherwise errors will occur.

Currently, I am deploying using a dos batch file that calls a controlling script into which I have manually specified the order of script execution, like so:

BATCH FILE:

SQLCMD -S %SERVER_NAME% -d %DATABASE_NAME% -i "DeployProcedures.sql"

SQL Script (DeployProcedures.sql):

:r view1.sql
:r view2.sql
:r view3.sql
etc
:r proc1.sql
:r proc2.sql
:r proc1.sql
etc

This works, but it is cumbersome to have to constantly keep this up to date.

Is there any other way of doing this? I think I would even be happy with running the deploy 4 times, with suppressed or "do not fail" on errors for the first 3 iterations, and then only enable terminate on errors for the final iteration.

I would rather something self-authored rather than a commercial product like: http://solutioncenter.apexsql.com/re-order-script-to-avoid-dependency-based-errors/

EDIT: downvotes on a question regarding a problem for which someone actually bothered to go through the trouble to write a commercial application - sigh.

Answer 1

sys.sql_expression_dependencies is your friend.

An example:

-- Show all the things that all objects depend on
SELECT o.name AS [Object], o.[type] AS [ObjectType], t.name AS [DependsOn], t.type AS [DependentObjectType]
  FROM sys.objects o
       INNER JOIN sys.sql_expression_dependencies d ON o.object_id = d.referencing_id
       INNER JOIN sys.objects t ON d.referenced_id = t.object_id
 ORDER BY o.name, t.name

Basically, you can generate your file that runs the script in order based on the dependencies you find from this query. It'll take some massaging for your application, but this should be a good start. You can always add a filter to limit things to specific types, of course. You can pretty much create all the tables in any order you want. Views, Functions, and Stored Procedures (in that order) are a bit trickier... views especially can have recursive dependencies that can be difficult to deal with, but it's certainly possible (recursive CTEs can auto-generate order here as well).