작업중 신규 테이블 및 SP 등 모든 내용을 재 작업하는 경우도 있지만 ..
간단하게 기존 DB 를 복원하여 재 작업을 진행하는 경우가 많은데 .. ( 기초자료 재 등록 등 귀찮아서 )
번거로운 작업중 하나가 등록자료 및 테스트 자료 삭제가 되는데 .. 그 부분에 대한 자료를 찾다가 좋은 내용이 있어 메모한다.
** 아래 스크립트 내용중에 해당 부분을 수정하여 처리하면 됨. ( 한번 Drop 된건 복구가 안됨)
Disable => ALTER TABLE ['+ @tablename + '] NOCHECK .... Enable => ALTER TABLE ['+ @tablename + '] CHECK .... Delete => ALTER TABLE ['+ @tablename + '] Drop ....
/************************** 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
FK 체크 리스트 조회 쿼리
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
disable_fk_constraints.txt