Can I turn off identity checking in SQL Server 2005?
I have a set of database objects (tables, functions, views, stored procedures), each written in its own file (the constraints are in the same file as the table they are modifying) that I would like to execute in an arbitrary order ... Is this possible in SQL Server 2005?
Some objects as an example:
Table A (link table B)
Table B (links Function A)
Function A (links View A)
View A (link table C)
Should be performed in the following order:
Table C
View A Function A
Table B
Table A
If the scripts do not work, errors are raised about missing objects.
The reason I am asking is because in the project I am 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 ok, This requires the master script to be manually edited anytime the object is added to the schema. I would like to just execute each script as found on the filesystem.
source to share
In my experience, the most problematic issue is with views that can be referenced recursively. I once wrote a utility to iterate through scripts until all bugs were fixed. This only works when loading everything. The ordering was important - I think I was doing UDTs, tables, FKs, views (iteratively), SPs and UDFs (iteratively until we decided that SPs calling SPs were a bad idea, and UDFs were generally bad idea.)
source to share
If you script foreign keys into separate files, you can get rid of the table table dependencies if you run the FK script after all tables have been created.
As far as I know, functions and procedures only check for the existence of an object in JOIN clauses.
The only difficulty I found was views versus views, since defining a view requires the objects that the view depends on to exist.
source to share