Sunday, September 16, 2007

Disable all Foreign Keys

Recently I was told to clear up all user tables in the client's database SQL Server 2000 SP3.There are lots of contsrtaints and for this purpose we would like to disable them in order to delete all data. Try it on Northwind database before using on production. If you want to enable constraints ,simple replace NOCHECK with CHECK.

DECLARE @TableName nvarchar(257),
@ForeignKeyConstraintName sysname,
@SQLStatement nvarchar(4000)

DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS TableName,
QUOTENAME(CONSTRAINT_NAME) AS ForeignKeyConstraintName
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY' AND
OBJECTPROPERTY
(
OBJECT_ID
(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
),
'IsMSShipped') = 0

OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO
@TableName,
@ForeignKeyConstraintName
IF @@FETCH_STATUS = -1 BREAK
SET @SQLStatement =
N'ALTER TABLE ' +
@TableName +
N' NOCHECK CONSTRAINT ' +
@ForeignKeyConstraintName
RAISERROR (@SQLStatement, 0, 1) WITH NOWAIT
EXEC sp_executesql @SQLStatement
END
CLOSE TableList
DEALLOCATE TableList
GO

1 comment:

Razvan Socol said...

The data type of the @TableName and @ForeignKeyConstraintName variables does not take into account the fact that the QUOTENAME function can increase the size of a name from 128 characters to up to 258 characters.