我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
我知道这是一个很晚(非常晚)的回复,但我找到了这些简单的方法来找到所有的foreign_key_references。这是解决方案;
解决方案1:
EXEC SP_FKEYS 'MyTableName'; // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References.
解决方案2:
EXEC SP_HELP 'MyTableName'; // It'll show all ForeignKey references in a single table.
解决方案03:
// It'll show you the Column_Name with Referenced_Table_Name
SELECT
COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name,
OBJECT_NAME(f.parent_object_id) Table_Name
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'MyTableName'
希望这对你有很大帮助。: -)
其他回答
Mysql服务器有information_schema。REFERENTIAL_CONSTRAINTS表供参考,您可以通过表名或引用表名过滤它。
最简单的方法是使用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'
我知道这是一个很晚(非常晚)的回复,但我找到了这些简单的方法来找到所有的foreign_key_references。这是解决方案;
解决方案1:
EXEC SP_FKEYS 'MyTableName'; // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References.
解决方案2:
EXEC SP_HELP 'MyTableName'; // It'll show all ForeignKey references in a single table.
解决方案03:
// It'll show you the Column_Name with Referenced_Table_Name
SELECT
COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name,
OBJECT_NAME(f.parent_object_id) Table_Name
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'MyTableName'
希望这对你有很大帮助。: -)
通过@Gishu所做的工作,我能够在SQL Server 2005中生成并使用以下SQL
SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo,
c.name AS ForeignKeyColumn, o.name AS FK_Name
FROM sys.foreign_key_columns AS fk
INNER JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
INNER JOIN sys.columns AS c ON fk.parent_object_id = c.object_id
AND fk.parent_column_id = c.column_id
INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables
WHERE name = 'TableOthersForeignKeyInto')
ORDER BY TableWithForeignKey, FK_PartNo;
在一个查询中显示表,列和外键名称。
上面有一些不错的答案。但我更喜欢一个问题就能得到答案。 这段代码来自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