CODEWITHSUNDEEP
sqlsql-serversql-server-2008generate-scripts
Jyothi Srinivasa
Jyothi Srinivasa

Reputation: 701

Generate drop- create sql scripts for keys, constraints in sql server database

I need to generate drop and create scripts for all the keys, constraints in the SQL server database. This exercise is to generate upgrade scripts for the database in the target environment . I have the script, but its failing because I need to find the right order of the database objects like, parent, child table level in order to crate them. For example, to crate/drop indexes , basically i capture the indexes using the below query

'SELECT object_name(si.object_id)
,si.object_id
,si.NAME
,si.index_id
FROM sys.indexes si
LEFT JOIN information_schema.table_constraints tc ON si.NAME = tc.constraint_name AND object_name(si.object_id) = tc.table_name
WHERE objectproperty(si.object_id, 'IsUserTable') = 1    
ORDER BY object_name(si.object_id)
,si.index_id'

Let us know how do we include order /level of indexes. Similarly need to make sure primary,foreign,constraints while generating scripts.

Can you let me know what is the right way to capture this.

Upvotes: 0

Views: 683

Answers (1)

john McTighe
john McTighe

Reputation: 1181

If you have all your FK etc in place then you can use sys.dependencies to figure out the correct sequence. One lazy way is to just keep running the scripts and they will eventually all succeed. Eg if create table 1 depends on table 2 then the first time you run table 2 gets created and the 2nd time table 1 gets created. Easy enough to script...

Upvotes: 0

Related Questions