SQL Server에서 주어진 테이블을 참조하는 모든 외래 키를 나열하려면 어떻게합니까?



9 Answers

나는 SQL Server Management Studio에서 Database Diagramming 기능을 사용 하겠지만, 당신이 그걸 다 판결했기 때문에 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')
)
Question

SQL Server 데이터베이스에서 고도로 참조 된 테이블을 제거해야합니다. 테이블을 삭제하기 위해 제거해야하는 모든 외래 키 제약 조건 목록을 얻으려면 어떻게해야합니까?

(SQL은 관리 스튜디오의 GUI에서 클릭하기보다 선호됩니다.)




SELECT
  object_name(parent_object_id),
  object_name(referenced_object_id),
  name 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')



선택한 테이블과 관련된 외래 키를 가져옵니다. * _FIRSTABLENAME_SECONDTABLENAME 형식을 사용합니다.

 declare @tablename as varchar(MAX)
 SET @tablename = 'yourtablename'
 SELECT name
 FROM YOURDATABASE.sys.objects
 WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and (name LIKE '%_' + @tablename + 'empdb_%' or name LIKE '%_' + @tablename )

이것은보다 일반적인 형식입니다.

 SELECT name
 FROM YOURDATABASE_PROD.sys.objects
 WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT' and name LIKE '%' + @tablename + '%' and
 name NOT LIKE '[a-zA-Z0-9]' + @tablename + '%' and name NOT LIKE '%' + @tablename + '[a-zA-Z0-9]' 



@Gishu가 SQL Server 2005에서 다음 SQL을 생성하고 사용할 수 있었던 작업

SELECT t.name AS TableWithForeignKey, fk.constraint_column_id AS FK_PartNo, 
       c.name AS ForeignKeyColumn, o.name AS FK_Name 
  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
       INNER JOIN sys.objects AS o ON fk.constraint_object_id = o.object_id
  WHERE fk.referenced_object_id = (SELECT object_id FROM sys.tables 
                                        WHERE name = 'TableOthersForeignKeyInto')
  ORDER BY TableWithForeignKey, FK_PartNo;

어느 테이블, 열 및 외래 키 이름을 모두 하나의 쿼리에 표시합니다.




도움말을 보려면 표의 모든 외래 키를 스크립팅하는 방법을 참조하십시오.

업데이트 : 링크를 더 이상 사용할 수 없지만 관련 SQL이 관련 질문에 대한 대답 으로 복사되었습니다.

GUI를 통해 종속성을 볼 수도 있습니다.




 SELECT OBJECT_NAME(fk.parent_object_id) as ReferencingTable, 
        OBJECT_NAME(fk.constraint_object_id) as [FKContraint]
  FROM sys.foreign_key_columns as fk
 WHERE fk.referenced_object_id = OBJECT_ID('ReferencedTable', 'U')

이것은 외래 키 제약 조건 인 경우에만 관계를 보여줍니다. 내 데이터베이스는 분명히 FK 제약 조건보다 먼저 나온다. 일부 테이블에서는 참조 무결성을 적용하기 위해 트리거를 사용하고 때로는 관계를 나타 내기 위해 비슷하게 이름이 지정된 열 (아무것도 참조 무결성이 없음) 만있다.

다행스럽게도 우리는 일관된 네이밍 씬 (naming scene)을 가지고 있으므로 다음과 같이 참조 테이블과 뷰를 찾을 수 있습니다.

SELECT OBJECT_NAME(object_id) from sys.columns where name like 'client_id'

나는이 선택을 스크립트 생성을위한 기초로 사용하여 관련 테이블에서해야 할 작업을 수행했습니다.




나는 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



위의 좋은 대답. 그러나 나는 한 가지 질문으로 대답하는 것을 선호한다. 이 코드 조각은 sys.sp_helpconstraint (sys proc)에서 가져옵니다.

이것이 tbl과 관련된 외래 키가있는 경우 Microsoft가 찾는 방법입니다.

--setup variables. Just change 'Customer' to tbl you want
declare @objid int,
    @objname nvarchar(776)
select @objname = 'Customer'    
select @objid = object_id(@objname)

if exists (select * from sys.foreign_keys where referenced_object_id = @objid)
    select 'Table is referenced by foreign key' =
        db_name() + '.'
        + rtrim(schema_name(ObjectProperty(parent_object_id,'schemaid')))
        + '.' + object_name(parent_object_id)
        + ': ' + object_name(object_id)
    from sys.foreign_keys 
    where referenced_object_id = @objid 
    order by 1

답은 다음과 같습니다. test_db_name.dbo.Account : FK_Account_Customer




원래의 질문은 고도로 참조 된 테이블에 모든 외래 키 목록을 가져와 테이블을 제거 할 수 있도록 요청했습니다.

이 작은 쿼리는 모든 외래 키를 특정 테이블에 드롭하는 데 필요한 '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 절을 생략하십시오.




이 시도 :

sp_help 'TableName'



Related