Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Deleting Key Restraints
- Thread starter shootsie
- Start date
TheBugSlayer
Programmer
In EM you cand open the table in design mode, right click on the field and in the popup menu choose Check Constraints and select the Indexes/Keys tab.
- Thread starter
- #3
- Thread starter
- #6
We are making some major changes to a database that would require, in almost every instance, for us to disregard the keys. Eventually we will move the data back into a database with keys, but in the meantime I need to remove them.
BTW, the DTS job worked in that it created the same tables without keys, but the data in my database was not exported. Any suggestions?
BTW, the DTS job worked in that it created the same tables without keys, but the data in my database was not exported. Any suggestions?
- Thread starter
- #8
I'm having errors exporting in DTS because of the owners of some tables. I'm trying every combination of checking and unchecking to export owners, object level permissions, etc.
Does anyone have any other suggestions on how to remove the keys?
Does anyone have any other suggestions on how to remove the keys?
TheBugSlayer
Programmer
shootsie,
try ALTER TABLE with the DROP CONSTRAINT clause. Now you need to determine all the constraints on all the tables. I am sure the constraints are kept in some system table you need to figure out, just like all tables are in systables table. So I am thinking of a script in which you will create two cursors, one that will contain all table names and for each table in the cursor populate another one with its constraints. Now you have to loop through the constraints and DROP them one by one.
You could use the undocumented sp_MSForEachTable also instead of a cursor to loop trough the tables.
I hope this helps.
try ALTER TABLE with the DROP CONSTRAINT clause. Now you need to determine all the constraints on all the tables. I am sure the constraints are kept in some system table you need to figure out, just like all tables are in systables table. So I am thinking of a script in which you will create two cursors, one that will contain all table names and for each table in the cursor populate another one with its constraints. Now you have to loop through the constraints and DROP them one by one.
You could use the undocumented sp_MSForEachTable also instead of a cursor to loop trough the tables.
I hope this helps.
TheBugSlayer
Programmer
Actually constraints are kept in the 'sysobjects', xtype is PK.
- Status
Similar threads