我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
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而不是tablename
其他回答
最初的问题要求将所有外键的列表放入一个高度引用的表中,以便可以删除表。
这个小查询返回将所有外键放入特定表所需的“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 -子句以获取当前数据库中所有外键的删除命令。
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
我将使用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')
)
以下的解决方案对我来说很有效:
--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
以下是我认为在SQL Server 2016中处理这种情况的最佳实践。
你必须列出外键使用:
EXEC sp_fkeys 'TableName'
在这里你可以看到fk的全部信息。注意列FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME, UPDATE_RULE, DELETE_RULE是你需要删除外键并在截断后再次实现它们的信息。
你可以组织一个脚本如下:
-- EXEC sp_fkeys 'TableName'
-- DROP CONSTRAINTS: I drop one, here drop every constraint you desire.
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName
DROP CONSTRAINT IF EXISTS FK_TableName_OtherTable
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
-- TRUNCATE
BEGIN TRANSACTION
TRUNCATE TABLE TableName
GO
COMMIT
-- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desire
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.TableName ADD CONSTRAINT
FK_TableName_OtherTable FOREIGN KEY
(
Id_FK
) REFERENCES dbo.OtherTable
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
** UPDATE_RULE和DELETE_RULE的值可以在sp_fkeys的文档中看到: