How to Disable ALL Constraint In ALL Tables

[Resant]

I know to disable all constraint in a table just :
ALTER TABLE table_A CHECK CONSTRAINT ALL
but how to disable ALL tables at the same time?

Thanks anyway

[Simon Hayes]

See this post (and the rest of the thread):

http://groups.google.ch/group/comp.d...31cf43f4?hl=en

Simon

[LLik]

This should do what you need on your current database

declare @tablename varchar(30)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch next from c1 into @tablename
while ( @@fetch_status <> -1 )
begin
exec ( 'alter table ' + @tablename + ' check constraint all ')
fetch next from c1 into @tablename
end
deallocate c1
go

[jsfromynr]

Hi Resant,

May this query help you solve the problem
E.G I used only top 10 records you can list as many

select top 10 'ALTER TABLE '+name+' CHECK CONSTRAINT ALL '
from sysobjects where type='u'

Copy the Output to a Query Analyzer Window and Run it

If this solve your problem Please let me know.
With warm regards
Jatinder

[DW]

You'd have to write a gen script to spit out the statements, and then
paste those back into your SQL window to disable the constraints. It
would be something like

select 'alter table ', table_name, ' nocheck constraint ' name' from
sysobjects

This SQL won't work as it is, I only use it to suggest a strategy. It
would work if table_name was a column in sysobjects, but it doesn't
seem that it is. There must be something in the sys* tables which links
the constraint name to the table name. You'd need to do some kind of
join, likely on whatever SQL uses to identify the tables behind the
scenes.

[DW]

Ummm - isn't it 'nocheck constraint' to disable, and 'check constraint'
to enable?

I'm just working on a similar problem, so I want to get it straight.

[Resant]

Thanks 4 all,
But is it possible using DTS to execute the query and save the result
into text file and then load that file, afterwards execute it?

Thx,

Resant

[Martijn Tonies]

> This should do what you need on your current database

declare @tablename varchar(30)
declare c1 cursor for select name from sysobjects where type = 'U'
open c1
fetch next from c1 into @tablename
while ( @@fetch_status <> -1 )
begin
exec ( 'alter table ' + @tablename + ' check constraint all ')
fetch next from c1 into @tablename
end
deallocate c1
go

Easy iteration can be done via
master.sp_MSforeachtable :-)
--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com