Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #820

Re: How to disable all constraints

Show all headers | View raw

harish saharan (harishksaharan@gmail.com) writes:
>                          I have a MySQL database with lot of tables .
> and I want to disable all the constraints from all the table at the
> same time .How can i do it .And after do my work how can i enable all
> the constraints  again

If you have a MySQL database, comp.databases.mysql is over there. This 
newsgroup is for SQL Server.

For SQL Server you can do:

SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) +
       ' NOCHECK CONSTRAINT ALL'
FROM   sys.objects o
JOIN   sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.type = 'U'

Run this, copy results and paste into a query window.

To enable constraints again, change NOCHECK to WITH CHECK CHECK. (Yes, 
that's two CHECK in a row.)

And, again, this is not a solution for MySQL.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

How to disable all constraints harish saharan <harishksaharan@gmail.com> - 2011-11-22 23:51 -0800
  Re: How to disable all constraints Erland Sommarskog <esquel@sommarskog.se> - 2011-11-23 08:56 +0000

csiph-web