我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
我正在使用这个脚本来查找与外键相关的所有细节。 我正在使用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
其他回答
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中的一个固有表名。
以下的解决方案对我来说很有效:
--Eliminar las llaves foraneas
declare @query varchar(8000)
declare cursorRecorrerTabla cursor for
SELECT 'ALTER TABLE [PoaComFinH].['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' 'query'
FROM PoaComFinH.sys.foreign_key_columns fk
JOIN PoaComFinH.sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
JOIN PoaComFinH.sys.schemas sch ON referencingTable.schema_id = sch.schema_id
JOIN PoaComFinH.sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
JOIN PoaComFinH.sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
--3ro. abrir el cursor.
open cursorRecorrerTabla
fetch next from cursorRecorrerTabla
into @query
while @@fetch_status = 0
begin
--inicio cuerpo del cursor
print @query
exec(@query)
--fin cuerpo del cursor
fetch next from cursorRecorrerTabla
into @query
end
--cerrar cursor
close cursorRecorrerTabla
deallocate cursorRecorrerTabla
这个答案建立在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;
下面是我将使用的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,所以让我们看一个例子。
所以,假设我想删除Microsoft心爱的Northwind数据库中的Employees表,但是SQL Server告诉我一个或多个外键阻止我这样做。
上面的SQL命令将返回这些结果…
它显示有3个外键引用雇员表。换句话说,除非先删除这三个外键,否则不允许删除(删除)这个表。
在结果中,第一行是以下外键约束在结果中的显示方式。
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 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'
我使用这个选择作为生成一个脚本的基础,做我需要做的事情 相关的表格。