我需要在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
其他回答
不知道为什么没有人建议,但我使用sp_fkeys查询给定表的外键:
EXEC sp_fkeys 'TableName'
你也可以指定模式:
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'
在没有指定模式的情况下,文档声明如下:
如果没有指定pktable_owner,则默认表可见性规则 的基础DBMS应用程序。 在SQL Server中,如果当前用户拥有一个具有指定 Name,返回该表的列。如果pktable_owner不是 且当前用户不拥有具有指定属性的表 Pktable_name时,该过程查找具有指定 由数据库所有者拥有的Pktable_name。如果存在,那张桌子就是 返回列。
你可以通过以下查询找到:
SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'YourTableName'
AND COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) = 'YourColumnName'
order by OBJECT_NAME(FK.parent_object_id)
这个答案建立在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) '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
确定数据库中所有表的主键和唯一键…
这应该列出所有的约束条件,在最后你可以放入过滤器
/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in ('YOUR_TABLE_NAME')
or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;
如需参考,请阅读http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx