我需要删除SQL Server数据库中高度引用的表.如何获取我需要删除的所有外键约束的列表以便删除表？
(在管理工作室的GUI中,SQL答案比点击更好.)
不知道为什么没有人建议,但我sp_fkeys用来查询给定表的外键:
EXEC sp_fkeys 'TableName'
您还可以指定架构:
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'
如果不指定架构,文档将声明以下内容:
如果未指定pktable_owner,则应用基础DBMS的默认表可见性规则.
在SQL Server中,如果当前用户拥有具有指定名称的表,则返回该表的列.如果未指定pktable_owner且当前用户不拥有具有指定pktable_name的表,则该过程将查找具有数据库所有者拥有的指定pktable_name的表.如果存在,则返回该表的列.
我在SQL Server Management Studio中使用了数据库图表功能,但是由于你排除了这一点 - 这在SQL Server 2008中对我有用(没有2005).
获取引用表和列名列表...
select
t.name as TableWithForeignKey,
fk.constraint_column_id as FK_PartNo, c.
name as ForeignKeyColumn
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
where
fk.referenced_object_id = (select object_id
from sys.tables
where name = 'TableOthersForeignKeyInto')
order by
TableWithForeignKey, FK_PartNo
获取外键约束的名称
select distinct name from sys.objects where object_id in
( select fk.constraint_object_id from sys.foreign_key_columns as fk
where fk.referenced_object_id =
(select object_id from sys.tables where name = 'TableOthersForeignKeyInto')
)
这给你:
FK本身
FK所属的架构
" 引用表 "或具有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
试试这个 :
sp_help 'TableName'
您还应该注意对其他对象的引用.
如果该表被其他表高度引用,那么它可能也被其他对象高度引用,例如视图,存储过程,函数等.
我真的建议使用GUI工具,例如SSMS中的"查看依赖关系"对话框或像ApexSQL这样的免费工具搜索,因为如果只想用SQL来搜索其他对象中的依赖关系,则可能容易出错.
如果SQL是唯一的选择,你可以尝试这样做.
select O.name as [Object_Name], C.text as [Object_Definition] from sys.syscomments C inner join sys.all_objects O ON C.id = O.object_id where C.text like '%table_name%'
原始问题要求将所有外键的列表放入高度引用的表中,以便可以删除该表.
这个小查询返回将所有外键放入特定表所需的所有"drop foreign key"命令:
SELECT
'ALTER TABLE ['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' '[DropCommand]'
FROM sys.foreign_key_columns fk
JOIN sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
JOIN sys.schemas sch ON referencingTable.schema_id = sch.schema_id
JOIN sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
JOIN sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
WHERE referencedTable.name = 'MyTableName'
示例输出:
[DropCommand] ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK_OtherTable1_MyTable] ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK_OtherTable2_MyTable]
省略WHERE子句以获取当前数据库中所有外键的drop命令.
这是我将使用的SQL代码.
SELECT
f.name AS 'Name of Foreign Key',
OBJECT_NAME(f.parent_object_id) AS 'Table name',
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS 'Fieldname',
OBJECT_NAME(t.object_id) AS 'References Table name',
COL_NAME(t.object_id,fc.referenced_column_id) AS 'References fieldname',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] DROP CONSTRAINT [' + f.name + ']' AS 'Delete foreign key',
'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT [' +
f.name + '] FOREIGN KEY([' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ']) REFERENCES ' +
'[' + OBJECT_NAME(t.object_id) + '] ([' +
COL_NAME(t.object_id,fc.referenced_column_id) + '])' AS 'Create foreign key'
-- , delete_referential_action_desc AS 'UsesCascadeDelete'
FROM sys.foreign_keys AS f,
sys.foreign_key_columns AS fc,
sys.tables t
WHERE f.OBJECT_ID = fc.constraint_object_id
AND t.OBJECT_ID = fc.referenced_object_id
AND OBJECT_NAME(t.object_id) = 'Employees' -- Just show the FKs which reference a particular table
ORDER BY 2
SQL不是特别清楚,所以让我们看一个例子.
所以,假设我想把Employees表放在微软心爱的Northwind数据库中,但是SQL Server告诉我一个或多个外键阻止我这样做.
上面的SQL命令会返回这些结果......
它告诉我有3个外键引用该Employees表.换句话说,在第一次删除这三个外键之前,我不允许删除(删除)此表.
在结果中,第一行是如何在结果中显示以下外键约束.
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo]) REFERENCES [dbo].[Employees] ([EmployeeID])
倒数第二列显示了我需要用来删除其中一个外键的SQL命令,例如:
ALTER TABLE [Employees] DROP CONSTRAINT [FK_Employees_Employees]
...并且右侧列显示了用于创建它的SQL ...
ALTER TABLE [Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo]) REFERENCES [Employees] ([EmployeeID])
使用所有这些命令,您可以获得删除相关外键所需的一切,以便删除表,然后再重新创建.
唷.希望这可以帮助.
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
最简单的方法是在SQL中使用sys.foreign_keys_columns.这里的表包含所有外键的Object ID,以及它们的Referenced列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'
SELECT
object_name(parent_object_id),
object_name(referenced_object_id),
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
我正在使用此脚本查找与外键相关的所有详细信息.我正在使用INFORMATION.SCHEMA.下面是一个SQL脚本:
SELECT
ccu.table_name AS SourceTable
,ccu.constraint_name AS SourceConstraint
,ccu.column_name AS SourceColumn
,kcu.table_name AS TargetTable
,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
ORDER BY ccu.table_name
第一
EXEC sp_fkeys 'Table', 'Schema'
然后使用NimbleText来播放您的结果
上面有一些好的答案.但我更喜欢用一个查询得到答案.这段代码取自sys.sp_helpconstraint(sys proc)
如果有与tbl关联的外键,那就是Microsoft查找的方式.
--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.Account:FK_Account_Customer
SELECT
OBJECT_NAME(parent_object_id) 'Parent table',
c.NAME 'Parent column name',
OBJECT_NAME(referenced_object_id) 'Referenced table',
cref.NAME 'Referenced column name'
FROM
sys.foreign_key_columns fkc
INNER JOIN
sys.columns c
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.object_id
INNER JOIN
sys.columns cref
ON fkc.referenced_column_id = cref.column_id
AND fkc.referenced_object_id = cref.object_id where OBJECT_NAME(parent_object_id) = 'tablename'
如果要获取所有表的外键关系,则排除where子句,否则写入您的表名而不是tablename
京公网安备 11010802040832号 | 京ICP备19059560号-6 