我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
我需要在SQL Server数据库中删除一个高度引用的表。我如何才能得到所有外键约束的列表,我将需要删除以便删除表?
(SQL比在管理工作室的GUI中点击更可取)
当前回答
这个答案建立在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;
其他回答
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中的一个固有表名。
这个答案建立在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;
SELECT
object_name(parent_object_id),
object_name(referenced_object_id),
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
我在2008年及以后一直在使用它。它类似于列出的其他一些解决方案,但字段名称是适当的大小写,以处理特定于大小写的(LatBin)排序。此外,您可以为它提供一个表名,并检索该表的信息。
-->>SPECIFY THE DESIRED DB
USE ???
GO
/*********************************************************************************************
LIST OUT ALL PRIMARY AND FOREIGN KEY CONSTRAINTS IN A DB OR FOR A SPECIFIED TABLE
*********************************************************************************************/
DECLARE @tblName VARCHAR(255)
/*******************/
SET @tblName = NULL-->NULL will return all PK/FK constraints for every table in the database
/*******************/
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),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
AND ( O1.name = @tblName
OR O2.name = @tblName
OR @tblName IS null)
ORDER BY PKTABLE_NAME,FKTABLE_NAME
您还应该注意对其他对象的引用。
如果表被其他表高度引用,那么它也可能被其他对象(如视图、存储过程、函数等)高度引用。
我真的推荐GUI工具,如SSMS中的“查看依赖关系”对话框或免费工具,如ApexSQL Search,因为如果你只想用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%'