sql server删除外键_sqlserver删除外键

-- 查询指定数据库外键
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;