반응형
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
EXEC dbo.usr_Disable_Foreign_Keys 0 --0: 활성화
EXEC dbo.usr_Disable_Foreign_Keys 1 --1: 비활성화
출저 : 태기의 데이터베이스 때려잡기
반응형
'연구개발 > SQL2008' 카테고리의 다른 글
IP 주소 범위 체크 (0) | 2010.05.20 |
---|---|
오라클의 LPAD 함수 (0) | 2010.05.20 |
DBCC 명령문 (0) | 2010.05.06 |
추척파일을 테이블로 변환하기 (0) | 2010.05.04 |
로그파일이 없을 시 복원 (0) | 2010.05.03 |