Deleting Key Restraints

[shootsie]

I have a SQL database that I need to remove all key restraints from. Is there a system stored procedure or any tool that can help me to do this? Thanks!

 

[TheBugSlayer]

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.

 

[shootsie]

I need to delete _all_ keys from _all_ tables. It would take forever for me to do that in EM. Any suggestions?

 

[dmhirsch]

DTS it into a new database, and choose NOT to export the keys.

 

[SQLSister]

Just curious as this is not somethign people would normally need to do, why do you want to delete all the keys?

 

[shootsie]

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?

 

[SQLSister]

You can now export the data in DTS also.

 

[shootsie]

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?

 

[TheBugSlayer]

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.

 

[TheBugSlayer]

Actually constraints are kept in the 'sysobjects', xtype is PK.