sql server删除外键

最新推荐文章于 2024-08-21 15:14:24 发布

曹振华

最新推荐文章于 2024-08-21 15:14:24 发布

阅读量799

点赞数 10

分类专栏： sql server 文章标签：数据库 sql 删除外键

本文链接：https://blog.csdn.net/chadcao/article/details/136053211

版权

sql server 专栏收录该内容

77 篇文章 0 订阅

订阅专栏

-- 查询指定数据库外键
SELECT
    fk.name AS '外键名称',
    tp.name AS '表名',
    ref.name AS '参考表',
    col.name AS '列名'
FROM
    sys.foreign_keys AS fk
    INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id
    INNER JOIN sys.tables AS ref ON fk.referenced_object_id = ref.object_id
    INNER JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN sys.columns AS col ON fkc.parent_column_id = col.column_id AND fkc.parent_object_id = col.object_id
		WHERE DB_NAME() = 'Plan';
		
-- 删除单个外键
ALTER TABLE PLR_GraphicProgressAttached DROP CONSTRAINT RefPL_GraphicProgress232;


-- 删除指定库所有外键
DECLARE @SqlStatement NVARCHAR(MAX);
SET @SqlStatement = (
    SELECT STUFF((
            SELECT '; ALTER TABLE ' + tp.name + ' DROP CONSTRAINT ' + fk.name
            FROM sys.foreign_keys AS fk
            INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id
            INNER JOIN sys.tables AS ref ON fk.referenced_object_id = ref.object_id
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
            INNER JOIN sys.columns AS col ON fkc.parent_column_id = col.column_id AND fkc.parent_object_id = col.object_id
            WHERE DB_NAME() = 'Plan'
            FOR XML PATH('')
        ), 1, 1, '')
);
EXEC sp_executesql @SqlStatement;