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





10 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')
)
이란 where clause

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

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




이 시도 :

sp_help 'TableName'



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

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




여기에 사용할 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은 아니므로 예제를 살펴 보겠습니다.

그래서, 내가 사랑하는 Northwind 데이터베이스에 Employees 테이블을 놓으려고했지만 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])

이 모든 명령을 사용하면 관련 외래 키를 삭제하는 데 필요한 모든 것이 있으므로 테이블을 삭제 한 다음 나중에 다시 작성할 수 있습니다.

휴. 희망이 도움이됩니다.




이 스크립트를 사용하여 외래 키와 관련된 모든 세부 정보를 찾습니다. 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



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을 작성하십시오.




 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 제약 조건보다 먼저 나온다. 일부 테이블에서는 참조 무결성을 적용하기 위해 트리거를 사용하고 때로는 관계를 나타 내기 위해 비슷하게 이름이 지정된 열 (아무것도 참조 무결성이 없음) 만있다.

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

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

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




@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;

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




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



선택한 테이블과 관련된 외래 키를 가져옵니다. * _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]' 



Related