|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Disable all constraintsHi champs
How do I disable absolutely all constraints on a table? I just want to empty all my tables quick. /Many thanks Kurlan
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL' FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME ='Tblname' Show quote "Kurlan" <Kur***@discussions.microsoft.com> wrote in message news:2B0F1D51-EE02-4546-A534-9A93763FEA01@microsoft.com... > Hi champs > How do I disable absolutely all constraints on a table? > > I just want to empty all my tables quick. > > > /Many thanks Cool !
but unfortunatelly I still get an error: Cannot truncate table 'table' because it is being referenced by a FOREIGN KEY constraint. Show quote "Uri Dimant" wrote: > Kurlan > > SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + > QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL' > FROM INFORMATION_SCHEMA.TABLES > WHERE > OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + > QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 > AND TABLE_TYPE = 'BASE TABLE' > AND TABLE_NAME ='Tblname' > > > > > > > > "Kurlan" <Kur***@discussions.microsoft.com> wrote in message > news:2B0F1D51-EE02-4546-A534-9A93763FEA01@microsoft.com... > > Hi champs > > How do I disable absolutely all constraints on a table? > > > > I just want to empty all my tables quick. > > > > > > /Many thanks > > > I don't think that you have to do anything about any constraints -EXCEPT the
FK constraint. Remove the Foreign Key constraint from any other tables, and then put them back when you are finished. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Kurlan" <Kur***@discussions.microsoft.com> wrote in message news:A77CC7AC-0F05-4CDD-8EE1-D38919F9AC1F@microsoft.com... > Cool ! > but unfortunatelly I still get an error: > Cannot truncate table 'table' because it is being referenced by a FOREIGN > KEY constraint. > > "Uri Dimant" wrote: > >> Kurlan >> >> SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + >> QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL' >> FROM INFORMATION_SCHEMA.TABLES >> WHERE >> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + >> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 >> AND TABLE_TYPE = 'BASE TABLE' >> AND TABLE_NAME ='Tblname' >> >> >> >> >> >> >> >> "Kurlan" <Kur***@discussions.microsoft.com> wrote in message >> news:2B0F1D51-EE02-4546-A534-9A93763FEA01@microsoft.com... >> > Hi champs >> > How do I disable absolutely all constraints on a table? >> > >> > I just want to empty all my tables quick. >> > >> > >> > /Many thanks >> >> >> Kurlan wrote:
> How do I disable absolutely all constraints on a table? ALTER TABLE YourTable NOCHECK CONSTRAINT ALL(this only applies to foreign key constraints and check constraints) You may also want to execute: ALTER TABLE YourTable DISABLE TRIGGER ALL Razvan When you want to re-enable them, execute:
ALTER TABLE YourTable WITH CHECK CHECK CONSTRAINT ALL ALTER TABLE YourTable ENABLE TRIGGER ALL Razvan Razvan Socol wrote: Show quote > Kurlan wrote: > > How do I disable absolutely all constraints on a table? > > ALTER TABLE YourTable NOCHECK CONSTRAINT ALL > (this only applies to foreign key constraints and check constraints) > > You may also want to execute: > ALTER TABLE YourTable DISABLE TRIGGER ALL > > Razvan still get's the error:
Cannot truncate table 'table' because it is being referenced by a FOREIGN KEY constraint. Show quote "Razvan Socol" wrote: > When you want to re-enable them, execute: > > ALTER TABLE YourTable WITH CHECK CHECK CONSTRAINT ALL > ALTER TABLE YourTable ENABLE TRIGGER ALL > > Razvan > > Razvan Socol wrote: > > Kurlan wrote: > > > How do I disable absolutely all constraints on a table? > > > > ALTER TABLE YourTable NOCHECK CONSTRAINT ALL > > (this only applies to foreign key constraints and check constraints) > > > > You may also want to execute: > > ALTER TABLE YourTable DISABLE TRIGGER ALL > > > > Razvan > > Disabling an FK constraint will not let you do TRUNCATE TABLE. You have to *remove* (not disable)
the FK constraints that *references* this table. Or, DELETE instead of TRUNCATE TABLE, whichever you find more manageable. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Kurlan" <Kur***@discussions.microsoft.com> wrote in message news:105B7D8E-FF36-4F13-AF06-C1DA848066F3@microsoft.com... > still get's the error: > > Cannot truncate table 'table' because it is being referenced by a FOREIGN > KEY constraint. > > > "Razvan Socol" wrote: > >> When you want to re-enable them, execute: >> >> ALTER TABLE YourTable WITH CHECK CHECK CONSTRAINT ALL >> ALTER TABLE YourTable ENABLE TRIGGER ALL >> >> Razvan >> >> Razvan Socol wrote: >> > Kurlan wrote: >> > > How do I disable absolutely all constraints on a table? >> > >> > ALTER TABLE YourTable NOCHECK CONSTRAINT ALL >> > (this only applies to foreign key constraints and check constraints) >> > >> > You may also want to execute: >> > ALTER TABLE YourTable DISABLE TRIGGER ALL >> > >> > Razvan >> >> /Tak tibor
Show quote "Tibor Karaszi" wrote: > Disabling an FK constraint will not let you do TRUNCATE TABLE. You have to *remove* (not disable) > the FK constraints that *references* this table. Or, DELETE instead of TRUNCATE TABLE, whichever you > find more manageable. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Kurlan" <Kur***@discussions.microsoft.com> wrote in message > news:105B7D8E-FF36-4F13-AF06-C1DA848066F3@microsoft.com... > > still get's the error: > > > > Cannot truncate table 'table' because it is being referenced by a FOREIGN > > KEY constraint. > > > > > > "Razvan Socol" wrote: > > > >> When you want to re-enable them, execute: > >> > >> ALTER TABLE YourTable WITH CHECK CHECK CONSTRAINT ALL > >> ALTER TABLE YourTable ENABLE TRIGGER ALL > >> > >> Razvan > >> > >> Razvan Socol wrote: > >> > Kurlan wrote: > >> > > How do I disable absolutely all constraints on a table? > >> > > >> > ALTER TABLE YourTable NOCHECK CONSTRAINT ALL > >> > (this only applies to foreign key constraints and check constraints) > >> > > >> > You may also want to execute: > >> > ALTER TABLE YourTable DISABLE TRIGGER ALL > >> > > >> > Razvan > >> > >> > > Aren't the constraints there for a reason?
Also take a look at this article: http://milambda.blogspot.com/2006/07/column-dependencies-and-consequences.html ML --- http://milambda.blogspot.com/ Yes the constraints should be there, but a DELETE FROM table is very slow
when you just want all the tables to be clean and ready for new data. Show quote "ML" wrote: > Aren't the constraints there for a reason? > > Also take a look at this article: > http://milambda.blogspot.com/2006/07/column-dependencies-and-consequences.html > > > ML > > --- > http://milambda.blogspot.com/ Wouldn't it be quicker to just re-run your database create scripts to drop
and recreate the tables and other schema objects? You do have a script or scripts that will create all of your database objects under a source control system right? Show quote "Kurlan" <Kur***@discussions.microsoft.com> wrote in message news:0A86D4F3-DE71-47D9-B432-AF9D603E20A6@microsoft.com... > Yes the constraints should be there, but a DELETE FROM table is very slow > when you just want all the tables to be clean and ready for new data. > > > "ML" wrote: > >> Aren't the constraints there for a reason? >> >> Also take a look at this article: >> http://milambda.blogspot.com/2006/07/column-dependencies-and-consequences.html >> >> >> ML >> >> --- >> http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics