Can I disable identifier checking in SQL Server 2005?

Question

I have an assortment of database objects (tables, functions, views, stored procedures) each scripted into its own file (constraints are in the same file as the table they alter) that I'd like to be able execute in an arbitrary order. Is this possible in SQL Server 2005?

Some objects as an example:
Table A (references Table B)
Table B (references Function A)
Function A (references View A)
View A (references Table C)

Must be run in the following order:
Table C
View A
Function A
Table B
Table A

If the scripts are run out of order, errors about the missing objects are thrown.

The reason I ask is that in a project I'm working on we maintain each database object in its own file (for source control purposes), and then maintain a master script that creates each database object in the correct order. This requires the master script to be manually edited any time an object is added to the schema. I'd like to be able to just execute each script as it is found in the file system.

Answer 1

I found this page where the author has written a nice procedure for doing exactly what you are talking about. Sounds like you just need to have two versions of it, one for disabling the constraints and another to re-enable then.

Answer 2

If you script the foreign keys into separate files, you can get rid of table-table dependencies, if you run the FK script after creating all tables.

As far as I'm aware, functions and procedures check for object existence only in JOIN clauses.

The only difficulty I found was views depending on views, as a view definition requires that the objects the view depends on do exist.

Answer 3

In my experience the most problematic issue is with views, which can reference recursively. I once wrote a utility to iterate through the scripts until the errors were all resolved. Which only works when you're loading everything. Order was important - I think I did UDTs, tables, FKs, views (iteratively), SPs and UDFs (iteratively until we decided that SPs calling SPs was a bad idea, and UDFs are generally a bad idea.)

Answer 4

APEX SQL Script is supposed to analyze the dependencies and order the script appropriately, but even then I've had problems.