February 7, 2003 at 10:33 am
I need to do some cleanup on a table and I want to delete from a table while temporary disabling the constraints. Is there is a quick way to do this in a sql statement without actually deleting the constraints.
February 7, 2003 at 12:27 pm
no
February 7, 2003 at 12:48 pm
I wrote this stored proc to print out the sql to add / remove all check constraints for a db, you could modify it to do what you want.
EXEC it this way : EXEC spGetConstraintsSQL 0
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGetConstraintsSQL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGetConstraintsSQL]
GO
CREATE PROCEDURE spGetConstraintsSQL(@ForSqlCompare bit = 1)
AS
DECLARE @crlf char(2),
@exists_sql_1 varchar(1000),
@constraint_exists_sql_2 varchar(1000),
@table_exists_sql_2 varchar(1000),
@sql_compare_sql varchar(200),
@dashes varchar(100),
@stars varchar(100)
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET @crlf = CHAR(13) + CHAR(10)
SET @exists_sql_1 = 'if exists (select * from sysobjects where id=object_id('
SET @constraint_exists_sql_2 = ') and OBJECTPROPERTY(id, ''IsCheckCnst'')=1)'
SET @table_exists_sql_2 = ') and OBJECTPROPERTY(id, ''IsTable'')=1)'
IF @ForSqlCompare = 1
SET @sql_compare_sql = @crlf
+ 'GO' + @crlf
+ 'IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION' + @crlf
+ 'GO' + @crlf
+ 'IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END' + @crlf
+ 'GO'
ELSE
SET @sql_compare_sql = @crlf + 'GO'
SET @dashes = REPLICATE('-', 80)
SET @stars = '--' + REPLICATE('*', 76) + '--'
PRINT @dashes
PRINT @stars
PRINT '-- CONSTRAINTS DROP STATEMENTS'
PRINT @stars
PRINT @dashes
SELECT @exists_sql_1 + '''' + ck.CONSTRAINT_NAME + '''' + @constraint_exists_sql_2
+ @crlf + CHAR(9)
+ 'ALTER TABLE '
+ ck.CONSTRAINT_SCHEMA + '.'
+ tck.TABLE_NAME
+ ' DROP CONSTRAINT '
+ ck.CONSTRAINT_NAME
+ @sql_compare_sql + @crlf + @crlf
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ck
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tck
ON ck.CONSTRAINT_NAME = tck.CONSTRAINT_NAME
PRINT @dashes
PRINT @stars
PRINT '-- END CONSTRAINTS DROP STATEMENTS'
PRINT @stars
PRINT @dashes
PRINT ''
PRINT ''
PRINT ''
PRINT @dashes
PRINT @stars
PRINT '-- CONSTRAINTS ADD STATEMENTS'
PRINT @stars
PRINT @dashes
SELECT @exists_sql_1 + '''' + ck.CONSTRAINT_SCHEMA + '.' + tck.TABLE_NAME + '''' + @table_exists_sql_2
+ @crlf + CHAR(9)
+ 'ALTER TABLE '
+ ck.CONSTRAINT_SCHEMA + '.'
+ tck.TABLE_NAME
+ ' ADD CONSTRAINT '
+ ck.CONSTRAINT_NAME
+ ' CHECK ' + ck.CHECK_CLAUSE
+ @sql_compare_sql + @crlf + @crlf
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ck
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tck
ON ck.CONSTRAINT_NAME = tck.CONSTRAINT_NAME
PRINT @dashes
PRINT @stars
PRINT '-- END CONSTRAINTS ADD STATEMENTS'
PRINT @stars
PRINT @dashes
SET NOCOUNT OFF
SET QUOTED_IDENTIFIER OFF
/*
Sample Calls:
EXEC spGetConstraintsSQL
EXEC spGetConstraintsSQL 1
EXEC spGetConstraintsSQL 0
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
February 7, 2003 at 2:03 pm
John, there is a way:
http://www.sqlservercentral.com/scripts/contributions/25.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
February 7, 2003 at 2:10 pm
Brians solution is much cleaner and simpler John. Just finished adding it to my briefcase..
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
February 7, 2003 at 2:46 pm
The key are these
to disable
ALTER TABLE tablename NOCHECK CONSTRAINT all
ALTER TABLE tablename DISABLE TRIGGER all
to enable
ALTER TABLE tablename CHECK CONSTRAINT all
ALTER TABLE tablename ENABLE TRIGGER all
Just replace tablename with of course your table name if it is a specific table you need to turn this off/on on.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply