SQL Server의 테이블을 참조하는 FOREIGN KEY 제약 조건을 어떻게 알 수 있습니까?
테이블을 삭제하려고하는데 다음 메시지가 표시됩니다.
메시지 3726, 수준 16, 상태 1, 줄 3
'dbo.UserProfile'개체가 FOREIGN KEY 제약 조건에 의해 참조되기 때문에 삭제할 수 없습니다.
메시지 2714, 수준 16, 상태 6, 줄 2
데이터베이스에 'UserProfile'이라는 개체가 이미 있습니다.
SQL Server Management Studio를 둘러 보았지만 제약 조건을 찾을 수 없습니다. 외래 키 제약 조건을 어떻게 찾을 수 있습니까?
여기있어:
SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'
이렇게하면 참조 테이블과 열 이름을 얻을 수 있습니다.
주석 제안에 따라 일반 sys.objects 대신 sys.tables를 사용하도록 편집되었습니다. 감사합니다, marc_s
또 다른 방법은 결과를 확인하는 것입니다.
sp_help 'TableName'
(또는 인용 된 TableName을 강조 표시하고 Alt + F1을 누르십시오.)
시간이 지남에 따라 나는 대답을 수정하기로 결정했습니다. 아래는 sp_help제공 되는 결과의 스크린 샷입니다 . A는이 예제에 AdventureWorksDW2012 DB를 사용했습니다. 여기에는 많은 좋은 정보가 있으며 우리가 찾고있는 것은 맨 끝에 녹색으로 강조 표시되어 있습니다.
이 시도
SELECT
object_name(parent_object_id) ParentTableName,
object_name(referenced_object_id) RefTableName,
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Tablename')
이 답변은 매우 간단하고 필요한 작업을 수행했습니다. https://stackoverflow.com/a/12956348/652519
링크의 요약은 다음 쿼리를 사용합니다.
EXEC sp_fkeys 'TableName'
빠르고 간단합니다. 15 개 테이블의 모든 외래 키 테이블, 각 열 및 외래 키 이름을 매우 빠르게 찾을 수있었습니다.
@mdisibio가 아래에 언급했듯이 다음은 사용할 수있는 다양한 매개 변수를 자세히 설명하는 설명서 링크입니다. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp- fkeys-transact-sql
이 스크립트를 사용하여 외래 키와 관련된 모든 세부 정보를 찾습니다. 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
개체 탐색기 창에서 SSMS를 통해 이동하려면 드롭하려는 개체를 마우스 오른쪽 단추로 클릭하고 종속성을 확인하십시오.
모든 데이터베이스에서 외래 키 관계를 찾는 가장 좋은 방법은 다음과 같습니다.
exec sp_helpconstraint 'Table Name'
그리고 한 가지 더
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='Table Name'
--and left(CONSTRAINT_NAME,2)='FK'(If you want single key)
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;
-다음은 원하는 정보를 더 많이 제공 할 수 있습니다.
create Procedure spShowRelationShips
(
@Table varchar(250) = null,
@RelatedTable varchar(250) = null
)
as
begin
if @Table is null and @RelatedTable is null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
order by 2,3
if @Table is not null and @RelatedTable is null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.Parent_Object_id) =@Table
order by 2,3
if @Table is null and @RelatedTable is not null
select object_name(k.constraint_object_id) ForeginKeyName,
object_name(k.Parent_Object_id) TableName,
object_name(k.referenced_object_id) RelatedTable,
c.Name RelatedColumnName,
object_name(rc.object_id) + '.' + rc.name RelatedKeyField
from sys.foreign_key_columns k
left join sys.columns c on object_name(c.object_id) = object_name(k.Parent_Object_id) and c.column_id = k.parent_column_id
left join sys.columns rc on object_name(rc.object_id) = object_name(k.referenced_object_id) and rc.column_id = k.referenced_column_id
where object_name(k.referenced_object_id) =@RelatedTable
order by 2,3
end
Foreign Keys@LittleSweetSeas 답변을 적용 하여 에 대한 모든 정보를 반환 할 수도 있습니다 .
SELECT
OBJECT_NAME(f.parent_object_id) ConsTable,
OBJECT_NAME (f.referenced_object_id) refTable,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM
sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN
sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
order by
ConsTable
In SQL Server Management Studio you can just right click the table in the object explorer and select "View Dependencies". This would give a you a good starting point. It shows tables, views, and procedures that reference the table.
try the following query.
select object_name(sfc.constraint_object_id) AS constraint_name,
OBJECT_Name(parent_object_id) AS table_name ,
ac1.name as table_column_name,
OBJECT_name(referenced_object_id) as reference_table_name,
ac2.name as reference_column_name
from sys.foreign_key_columns sfc
join sys.all_columns ac1 on (ac1.object_id=sfc.parent_object_id and ac1.column_id=sfc.parent_column_id)
join sys.all_columns ac2 on (ac2.object_id=sfc.referenced_object_id and ac2.column_id=sfc.referenced_column_id)
where sfc.parent_object_id=OBJECT_ID(<main table name>);
this will give the constraint_name, column_names which will be referring and tables which will be depending on the constraint will be there.
You could use this query to display Foreign key constaraints:
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='YourTable'
The easiest way to get Primary Key and Foreign Key for a table is:
/* Get primary key and foreign key for a table */
USE DatabaseName;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'PK%' AND
TABLE_NAME = 'TableName'
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME LIKE 'FK%' AND
TABLE_NAME = 'TableName'
'developer tip' 카테고리의 다른 글
| 지원되지 않는 메서드 : 프로젝트 실행 중 AndroidProject.getPluginGeneration () (0) | 2020.08.14 |
|---|---|
| 패키지 R이 없습니다. (0) | 2020.08.14 |
| 로그없이 SQL에서 테이블의 대용량 데이터를 삭제하는 방법은 무엇입니까? (0) | 2020.08.14 |
| 프로그래밍 방식으로 모서리를 둥글게하고 임의의 배경색을 설정하는 방법 (0) | 2020.08.14 |
| 1 열 테이블이 좋은 디자인입니까? (0) | 2020.08.14 |