471,050 Members | 1,366 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

How to Disable ALL Constraint In ALL Tables

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

Jul 23 '05 #1
7 15369
See this post (and the rest of the thread):

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

Simon

Jul 23 '05 #2
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

Jul 23 '05 #3
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

Jul 23 '05 #4
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.

Jul 23 '05 #5
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.

Jul 23 '05 #6
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

Jul 23 '05 #7
> 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
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by yongsing | last post: by
25 posts views Thread by Thomas R. Hummel | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.