SQL Server 데이터베이스에서 참조가 높은 테이블을 제거해야합니다. 테이블을 삭제하기 위해 제거해야 할 모든 외래 키 제약 조건의 목록을 어떻게 얻을 수 있습니까?
(관리 스튜디오의 GUI에서 클릭하는 것보다 SQL이 선호합니다.)
답변
왜 아무도 제안하지 않았지만 sp_fkeys주어진 테이블에 대한 외래 키를 쿼리 하는 데 사용 합니다.
EXEC sp_fkeys 'TableName'스키마를 지정할 수도 있습니다.
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'스키마를 지정하지 않으면 문서 는 다음을 나타냅니다.
pktable_owner를 지정하지 않으면 기본 DBMS의 기본 테이블 가시성 규칙이 적용됩니다.
SQL Server에서 현재 사용자가 지정된 이름의 테이블을 소유 한 경우 해당 테이블의 열이 반환됩니다. pktable_owner를 지정하지 않고 현재 사용자가 지정된 pktable_name을 가진 테이블을 소유하지 않으면 프로시 저는 데이터베이스 소유자가 소유 한 지정된 pktable_name을 가진 테이블을 찾습니다. 존재하는 경우 해당 테이블의 열이 반환됩니다.
답변
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')
)
답변
이것은 당신에게 제공합니다 :
- FK 자체
- FK가 속한 스키마
- ” 참조 테이블 “또는 FK가있는 테이블
- ” 참조 열 “또는 FK를 가리키는 참조 테이블 내부의 열
- ” 참조 테이블 “또는 FK가 가리키는 키 열이있는 테이블
- ” 참조 된 열 “또는 FK가 가리키는 키인 열
아래 코드 :
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
답변
이 시도 :
sp_help 'TableName'답변
다른 객체에 대한 참조도 염두에 두어야합니다.
테이블이 다른 테이블에 의해 고도로 참조 된 경우, 뷰, 스토어드 프로 시저, 함수 등과 같은 다른 오브젝트에 의해 참조 될 수도 있습니다.
SSMS의 ‘종속성보기’대화 상자와 같은 GUI 도구 또는 ApexSQL 검색 과 같은 무료 도구를 사용하는 것이 좋습니다 . 다른 객체에서 종속성을 검색하면 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%'
답변
원래 질문은 테이블을 제거 할 수 있도록 모든 외래 키 목록을 참조가 높은 테이블로 가져 오도록 요청했습니다.
이 작은 쿼리는 모든 외래 키를 특정 테이블에 삭제하는 데 필요한 모든 ‘외래 키 삭제’명령을 반환합니다.
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-clause를 생략하여 현재 데이터베이스의 모든 외래 키에 대한 삭제 명령을 가져 오십시오.
답변
내가 사용할 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은 아니므로 예제를 살펴 보겠습니다.
따라서 EmployeesMicrosoft의 사랑받는 Northwind데이터베이스에 테이블 을 삭제하려고한다고 가정 했지만 SQL Server는 하나 이상의 외래 키로 인해이 작업을 수행하지 못하게한다고 말했습니다.
위의 SQL 명령은 이러한 결과를 반환합니다 …
Employees테이블 을 참조하는 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])이러한 모든 명령을 사용하면 테이블을 삭제 한 다음 나중에 다시 만들 수 있도록 관련 외래 키를 삭제하는 데 필요한 모든 것이 있습니다.
휴 도움이 되었기를 바랍니다.
