1. 먼저 아래의 저장 프로시저를 작성하여 컴파일한다.
|
CREATE PROCEDURE dbo.usr_Disable_Foreign_Keys
@disable BIT = 1
AS
DECLARE
@sql VARCHAR(500),
@schemaName VARCHAR(128),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128)
DECLARE foreignKeyCursor CURSOR
FOR
SELECT
ref.constraint_name AS FK_Name,
fk.table_schema AS FK_Schema,
fk.table_name AS FK_Table
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
ON ref.constraint_name = fk.constraint_name
ORDER BY
fk.table_schema,
fk.table_name,
ref.constraint_name
OPEN foreignKeyCursor
FETCH NEXT FROM foreignKeyCursor
INTO @foreignKeyName, @schemaName, @tableName
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @disable = 1
SET @sql = 'ALTER TABLE ['
+ @schemaName + '].['
+ @tableName + '] NOCHECK CONSTRAINT ['
+ @foreignKeyName + ']'
ELSE
SET @sql = 'ALTER TABLE ['
+ @schemaName + '].['
+ @tableName + '] CHECK CONSTRAINT ['
+ @foreignKeyName + ']'
PRINT 'Executing Statement - ' + @sql
EXECUTE(@sql)
FETCH NEXT FROM foreignKeyCursor
INTO @foreignKeyName, @schemaName, @tableName
END
CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
GO
|
2. 모든 외래키 활성화 및 비활성화하기
|
Exec dbo.usr_Disable_Foreign_Keys 0 -- 0: 활성화
Exec dbo.usr_Disable_Foreign_Keys 1 -- 1: 비활성화
|
3. 실행 예
다음 예는 AdventureWoks 예제 데이터 베이스에서 외래키를 비활성화 시키는 예이다.
|
USE AdventureWorks
GO
Exec dbo.usr_Disable_Foreign_Keys 1
GO
Result>>
Executing Statement - ALTER TABLE [HumanResources].[Employee] NOCHECK CONSTRAINT [FK_Employee_Contact_ContactID]
Executing Statement - ALTER TABLE [HumanResources].[Employee] NOCHECK CONSTRAINT [FK_Employee_Employee_ManagerID]
Executing Statement - ALTER TABLE [HumanResources].[EmployeeAddress] NOCHECK CONSTRAINT [FK_EmployeeAddress_Address_AddressID]
Executing Statement - ALTER TABLE [HumanResources].[EmployeeAddress] NOCHECK CONSTRAINT [FK_EmployeeAddress_Employee_EmployeeID]
Executing
Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
NOCHECK CONSTRAINT
[FK_EmployeeDepartmentHistory_Department_DepartmentID]
Executing
Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
NOCHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID]
Executing
Statement - ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]
NOCHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
Executing
Statement - ALTER TABLE [HumanResources].[EmployeePayHistory] NOCHECK
CONSTRAINT [FK_EmployeePayHistory_Employee_EmployeeID]
...
...
...
|