Is there any way to disable/enable all constraints in a database ?

Is there any way to disable/enable all constraints in a database ?To Disable
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
To Re-enable
sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
go
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:uCjyRYwlDHA.3024@.tk2msftngp13.phx.gbl...
>
>|||Note though that this does no affect Primary Key, Unique or Default constraints.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message news:ek03%23fwlDHA.3320@.tk2msftngp13.phx.gbl...
> To Disable
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
> To Re-enable
> sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
> go
>
> "Vlad Gonchar" <VladG@.Frogware.com> wrote in message
> news:uCjyRYwlDHA.3024@.tk2msftngp13.phx.gbl...
> >
> >
> >
>|||In my book online there is no "sp_msforeachtable" stored procedure.
Issuing SELECT @.@.VERSION gives the following:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
"Mike" <Mike@.Comcast.net> wrote in message
news:ek03#fwlDHA.3320@.tk2msftngp13.phx.gbl...
> To Disable
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
> To Re-enable
> sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
> go
>
> "Vlad Gonchar" <VladG@.Frogware.com> wrote in message
> news:uCjyRYwlDHA.3024@.tk2msftngp13.phx.gbl...
> >
> >
> >
>|||sp_msforeachtable is an "undocumented" system stored procedure so you won't
find it in BOL.
On a seperate note make sure you are protected from Slammer
http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp
http://www.microsoft.com/sql/downloads/2000/sp3.asp
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Vlad Gonchar" <VladG@.Frogware.com> wrote in message
news:%23qtnD0wlDHA.360@.TK2MSFTNGP12.phx.gbl...
In my book online there is no "sp_msforeachtable" stored procedure.
Issuing SELECT @.@.VERSION gives the following:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
"Mike" <Mike@.Comcast.net> wrote in message
news:ek03#fwlDHA.3320@.tk2msftngp13.phx.gbl...
> To Disable
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
> To Re-enable
> sp_msforeachtable"ALTER TABLE ? CHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
> go
>
> "Vlad Gonchar" <VladG@.Frogware.com> wrote in message
> news:uCjyRYwlDHA.3024@.tk2msftngp13.phx.gbl...
> >
> >
> >
>|||You can find it in master database.