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

最新推荐文章于 2021-05-30 22:51:39 发布

code_long

最新推荐文章于 2021-05-30 22:51:39 发布

阅读量1k

点赞数

分类专栏： SQL Server 文章标签： SQL Server

本文链接：https://blog.csdn.net/code_long/article/details/11210155

版权

SQL Server 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--禁止约束
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