0

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.

Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
Jyothi Srinivasa
  • 701
  • 2
  • 9
  • 26
  • Possible duplicate of [How can I automate the "generate scripts" task in SQL Server Management Studio 2008?](http://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2) – TheGameiswar Feb 24 '17 at 10:10
  • Thanks, but these are create scripts for new databases, I need to append ifnot exists and also drop and create scripts exclusively for database upgrades for the target environments – Jyothi Srinivasa Feb 24 '17 at 10:57
  • See http://dba.stackexchange.com/a/24842/44324 and http://stackoverflow.com/a/31191116/2224701 – Vojtěch Dohnal Feb 24 '17 at 12:26

1 Answers1

0

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...

john McTighe
  • 1,181
  • 6
  • 8