• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Drop All Constraints?

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic