Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: How to disable all constraints Date: Wed, 23 Nov 2011 08:56:13 +0000 (UTC) Organization: Erland Sommarskog Lines: 33 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Wed, 23 Nov 2011 08:56:13 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="G7+Jz22XqYCG8C6rb1H3YA"; logging-data="10911"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19oClCfcotmt/bwEupiGg2b" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:Agstwdn4+0aPy7mL3mmU8rL0I+I= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:820 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