我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
这个答案建立在sp_fkeys的基础上,但格式化类似于sp_fkeys,适用于多个列并列出它们的顺序。
SELECT fk_obj.name AS FK_NAME,
pk_schema.name AS PKTABLE_OWNER,
pk_table.name AS PKTABLE_NAME,
pk_column.name AS PKCOLUMN_NAME,
fk_schema.name AS FKTABLE_OWNER,
fk_table.name AS FKTABLE_NAME,
fk_column.name AS FKCOLUMN_NAME,
ROW_NUMBER() over (
PARTITION BY fk_obj.name, fk_schema.name
ORDER BY fkc.constraint_column_id
) AS KEY_SEQ
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects fk_obj
ON fk_obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables fk_table
ON fk_table.object_id = fkc.parent_object_id
INNER JOIN sys.schemas fk_schema
ON fk_table.schema_id = fk_schema.schema_id
INNER JOIN sys.columns fk_column
ON fk_column.column_id = parent_column_id
AND fk_column.object_id = fk_table.object_id
INNER JOIN sys.tables pk_table
ON pk_table.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas pk_schema
ON pk_table.schema_id = pk_schema.schema_id
INNER JOIN sys.columns pk_column
ON pk_column.column_id = fkc.referenced_column_id
AND pk_column.object_id = pk_table.object_id;
其他回答
这会给你:
FK本身 FK所属的Schema “引用表”或者有FK的表 “引用列”或引用表中指向FK的列 “引用表”或具有FK指向的键列的表 “引用列”或者是FK指向的键的列
下面的代码:
SELECT obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
Oracle SQL
select *
from
all_constraints
where
r_constraint_name in
(select constraint_name
from
all_constraints
where
table_name='PUT_THE_TABLE_NAME_HERE');
all_constraints是Oracle DB中的一个固有表名。
Mysql服务器有information_schema。REFERENTIAL_CONSTRAINTS表供参考,您可以通过表名或引用表名过滤它。
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
-- Force the column to be non-nullable (see SQL BU 325751)
--KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')
WHEN 1 THEN 0
ELSE 1
END),
DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')
WHEN 1 THEN 0
ELSE 1
END),
FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
PK_NAME = CONVERT(SYSNAME,I.NAME),
DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE
FROM SYS.ALL_OBJECTS O1,
SYS.ALL_OBJECTS O2,
SYS.ALL_COLUMNS C1,
SYS.ALL_COLUMNS C2,
SYS.FOREIGN_KEYS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
INNER JOIN SYS.INDEXES I
ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
最简单的方法是使用sys。SQL中的foreign_keys_columns。这里的表包含了所有外键的对象ID,包括它们的引用列ID、引用表ID以及引用列和表。由于Id保持不变,因此对于Schema和表中的进一步修改,结果将是可靠的。
查询:
SELECT
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id)
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys
我们还可以使用where添加过滤器
WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'