Here is a very usefull script from decipherinfosys
I tried this out on SQL server 2000. Its working fine.
/************** DISABLE ALL TABLE CONSTRAINTS *********************************
This script will disable all constraints on all tables within the database
that it is run in.
******************************************************************************/
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)
--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
name AS constraintname,
object_name(parent_obj) AS tablename
INTO #Const_Table
FROM sysobjects s
where xtype in ('F')
SELECT @Count = Count(*) FROM #Const_Table
--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1
--Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @ConstraintName = ConstraintName
FROM #Const_Table
--Build execution string to disable constraint.
SET @String = 'ALTER TABLE ['+ @tablename + '] NOCHECK CONSTRAINT [' + @constraintname +']'
--Execute the SQL
exec sp_executesql @string
--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName
SET @Count = @Count - 1
END -- Loop
set rowcount 0
After having re-enabled the constrains you can check constraints using the following command:
DBCC CHECKCONSTRAINTS
And look at your constraints using this command:
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
No comments:
Post a Comment