禁用启用数据库外键约束

-- =====================================================
-- 删除数据库中的所有数据(通过禁用约束)
-- 不好的地方在于如果本身有已经禁用的约束,执行这个后会开启它

-- 邹建 2005.09(引用请保留此信息)
-- =====================================================
-- 禁用所有约束
EXEC sp_msforeachtable
    @command1=N'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- 删除无外键约束的表中的数据
EXEC sp_msforeachtable
    @command1=N'TRUNCATE TABLE ?',
    @whereand=N'AND NOT EXISTS(
                    SELECT rkeyid FROM dbo.sysforeignkeys
                    WHERE rkeyid=O.id)
                AND EXISTS(
                    SELECT rows FROM dbo.sysindexes
                    WHERE id=O.id
                        AND rows>0
                        AND indid<2)'

-- 删除有外键约束的表中的数据
EXEC sp_msforeachtable
    @command1=N'DELETE ?',
    @whereand=N'AND EXISTS(
                    SELECT rows FROM dbo.sysindexes
                    WHERE id=O.id
                        AND rows>0
                        AND indid<2)'

-- 启用所有约束
EXEC sp_msforeachtable
    @command1=N'ALTER TABLE ? CHECK CONSTRAINT ALL'