SQL Server 禁用外键约束和启用外键约束及删除表数据

关注

原创最新推荐文章于 2026-06-13 14:00:00 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

SQLServer禁用、启用外键约束 SQL Server 临时禁用和启用所有外键约束阅读详情

--禁止约束
DECLARE DEL_CONSTRAINT CURSOR FOR
	SELECT 'ALTER TABLE '+OBJECT_NAME(PARENT_OBJ)+' NOCHECK CONSTRAINT '+NAME+';'
	FROM SYSOBJECTS A LEFT OUTER JOIN SYSCOMMENTS B ON A.ID = B.ID
	WHERE A.XTYPE = 'F' AND A.PARENT_OBJ IN (SELECT ID FROM SYSOBJECTS WHERE name LIKE 'Test%')
OPEN DEL_CONSTRAINT
DECLARE @DEL_CONSTRAINT VARCHAR(8000)
FETCH NEXT FROM DEL_CONSTRAINT INTO @DEL_CONSTRAINT
WHILE (@@fetch_status = 0)
	BEGIN
		PRINT @DEL_CONSTRAINT
		EXEC(@DEL_CONSTRAINT)
		FETCH NEXT FROM DEL_CONSTRAINT INTO @DEL_CONSTRAINT
	END
CLOSE DEL_CONSTRAINT
DEALLOCATE DEL_CONSTRAINT
GO
--删除表数据
DECLARE DEL_TABLES CURSOR FOR
	SELECT 'DELETE TABLE dbo.'+ NAME
	FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME LIKE 'Test%'
OPEN DEL_TABLES
DECLARE @DEL_TABLES VARCHAR(8000)
FETCH NEXT FROM DEL_TABLES INTO @DEL_TABLES
WHILE (@@fetch_status = 0)
	BEGIN
		PRINT @DEL_TABLES
		EXEC (@DEL_TABLES)
		FETCH NEXT FROM DEL_TABLES INTO @DEL_TABLES
	END
CLOSE DEL_TABLES
DEALLOCATE DEL_TABLES
GO
--启动约束
DECLARE DEL_CONSTRAINT CURSOR FOR
	SELECT 'ALTER TABLE '+OBJECT_NAME(PARENT_OBJ)+' CHECK CONSTRAINT '+NAME+';'
	FROM SYSOBJECTS A LEFT OUTER JOIN SYSCOMMENTS B ON A.ID = B.ID
	WHERE A.XTYPE = 'F' AND A.PARENT_OBJ IN (SELECT ID FROM SYSOBJECTS WHERE name LIKE 'Test%')
OPEN DEL_CONSTRAINT
DECLARE @DEL_CONSTRAINT VARCHAR(8000)
FETCH NEXT FROM DEL_CONSTRAINT INTO @DEL_CONSTRAINT
WHILE (@@fetch_status = 0)
	BEGIN
		PRINT @DEL_CONSTRAINT
		EXEC(@DEL_CONSTRAINT)
		FETCH NEXT FROM DEL_CONSTRAINT INTO @DEL_CONSTRAINT
	END
CLOSE DEL_CONSTRAINT
DEALLOCATE DEL_CONSTRAINT
GO

收录于