Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Drop All Constraints?

 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there a good way to drop all constraints on a table (in SQL Server)? I'd love to be able to do something like this:



Thanks.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its not quite that easy. In 2000 you could select everything from systobject where the parent_obj = your table and the xtype is a constraint, then use a cursor to go through these constructing and executing the "alter table [your table] drop constraint [current_constraint]" commands.

Have a look at the docs for sysobjects. That should get you going.

If you want to do this in a 2005 style, there are now four constraint views (sys.check_constraints, sys.default_constraints, sys.foreign_keys and sys.key_constraints) that will give you all the various constrains for your table.

Have a read of the docs for these tables. Its also a good think to know about the object_name and object_id functions. These will transform from an id into a name and back again. So typically you might use them to do this:

or something or other.
 
krishna chaitanya vemprala
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


By running the above query, it generates you a list of all tables and its respective constraints, the present query generates the alter table statements for removing primary and foreign keys only, you can add ('PK','F','U') to the list if you have unique keys also. copy the result list and execute that and the constraints will be disabled.

I hope the problem will be solved by this

Thank you.

Vemprala Chaitanya.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic