我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
这会给你:
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
其他回答
SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable,
OBJECT_NAME(fk.constraint_object_id) as [FKContraint]
FROM sys.foreign_key_columns as fk
WHERE fk.referenced_object_id = OBJECT_ID('ReferencedTable', 'U')
这只显示了外键约束的关系。我的数据库显然早于FK约束。一些表使用触发器来强制引用完整性,有时除了一个类似命名的列来指示关系之外什么都没有(根本没有引用完整性)。
幸运的是,我们有一个一致的命名场景,所以我能够找到引用表 观点是这样的:
SELECT OBJECT_NAME(object_id) from sys.columns where name like 'client_id'
我使用这个选择作为生成一个脚本的基础,做我需要做的事情 相关的表格。
也试一试。
EXEC sp_fkeys 'tableName', 'schemaName'
使用sp_fkeys,不仅可以通过pk表名和模式过滤结果,还可以通过fk表名和模式过滤结果。链接
这会给你:
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
上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自sys. .Sp_helpconstraint (sys proc)
这是微软查找是否有与tbl关联的外键的方法。
--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
@objname nvarchar(776)
select @objname = 'Customer'
select @objid = object_id(@objname)
if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
select 'Table is referenced by foreign key' =
db_name() + '.'
+ rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
+ '.' + object_name(parent_object_id)
+ ': ' + object_name(object_id)
from sys.foreign_keys
where referenced_object_id = @objid
order by 1
答案看起来像这样:test_db_name.dbo。账户:FK_Account_Customer
with tab_list as (
select t.name AS Table_Name, t.object_id, s.name AS Table_Schema from sys.tables t, sys.schemas s
where t.schema_id = s.schema_id
and s.name = 'your schema')
select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,
col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,
CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale)
WHEN col.max_length = -1 THEN 'max'
WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULL
ELSE CONVERT(NVARCHAR(4000), col.max_length / 2)
END
AS Length,
ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Column
from sys.COLUMNS col
INNER JOIN tab_list tab ON col.object_id = tab.object_id
INNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_id
LEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_id
LEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_id
LEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_id
LEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id