SP or TSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP or TSQL

Author

Topic

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-11-30 : 07:03:45

Hi
Is there any sp or command to deactivate and activate all constraints in a database...

Thanks in Advance

RKNAIR

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 07:10:22

You can enable/disable constraints at table level, not at DB level.

Alter Table <table>
NOCHECK CONSTRAINT ALL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-11-30 : 07:33:21

Thanks Harsh

I find the solution in DB level by giving
sp_msforeachtable @command1="alter table nocheck constraint all"

Regards

Ratheesh.K.Nair

RKNAIR

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 07:37:06

Beware of using undocumented MS commands. It may not be a good practice from maintainence standpoint.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-11-30 : 07:40:14

Can u please tell me the otherside of using undocumented ms commands

RKNAIR

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-30 : 08:09:40

any undocumented api is not guaranteed to work in future versions, even future service packs. so your code may suddenly be broken after installing a SP or upgrading to a new version. that's the main risk.

elsasoft.org

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-11-30 : 08:19:16

Thanks jezemine

RKNAIR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources