반응형
SQL Server에서 특정 테이블을 참조하는 모든 외부 키를 나열하려면 어떻게 해야 합니까?
SQL Server 데이터베이스에서 자주 참조되는 테이블을 제거해야 합니다.테이블을 드롭하기 위해 삭제해야 하는 모든 외부 키 제약사항 목록을 가져오려면 어떻게 해야 합니까?
(SQL은 관리스튜디오의 GUI에서 클릭하는 것보다 응답하는 것이 좋습니다.)
하지 않았는지 , 저는 이 말을 합니다.sp_fkeys'CHANGE: 'CHANGE: 'CHANGE: 'CHANGE:
EXEC sp_fkeys 'TableName'
스키마를 지정할 수도 있습니다.
EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'
스키마를 지정하지 않으면 docs는 다음과 같이 기술합니다.
pktable_owner를 지정하지 않으면 기본 DBMS의 기본 테이블 가시성 규칙이 적용됩니다.
SQL Server에서 현재 사용자가 지정된 이름의 테이블을 소유하면 해당 테이블의 열이 반환됩니다.pktable_owner가 지정되지 않은 경우 현재 사용자가 지정된 pktable_name의 테이블을 소유하지 않으면 데이터베이스 소유자가 소유한 지정된 pktable_name의 테이블을 찾습니다.존재하는 경우 해당 테이블의 열이 반환됩니다.
다음과 같은 이점을 얻을 수 있습니다.
- 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
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')
)
다음을 시도해 보십시오.
sp_help 'TableName'
다른 오브젝트에 대한 참조도 유의해야 합니다.
테이블이 다른 테이블에서 많이 참조된 경우 뷰, 저장 프로시저, 함수 등의 다른 개체에서도 많이 참조되었을 수 있습니다.
SSMS의 'view dependencies' 대화상자 등의 GUI 툴이나 ApexSQL Search 등의 무료 툴을 추천합니다.이는 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를 생략하여 현재 데이터베이스 내의 모든 외부 키에 대한 drop 명령을 가져옵니다.
여기 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은 그다지 명확하지 않으므로 예를 들어 보겠습니다.
만약에 하면,EmployeesNorthwind하지만 SQL Server에서 하나 이상의 외부 키로 인해 이 작업을 수행할 수 없다고 합니다.
위의 SQL 명령에서는 이러한 결과가 반환됩니다.
, 3개의 외부 키가 .Employees즉, 이 세 개의 외부 키가 처음 삭제될 때까지 이 테이블을 삭제할 수 없습니다.
결과에서 첫 번째 행은 다음과 같은 외부 키 제약 조건이 결과에 표시되는 방법입니다.
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])
이러한 명령어를 모두 사용하면 관련 외부 키를 삭제하여 테이블을 삭제한 후 나중에 다시 작성할 수 있습니다.
이게 도움이 됐으면 좋겠네요
가장 간단한 방법은 sys를 사용하는 것입니다.foreign_keys_columns를 지정합니다.이 표에는 Referencing Columns 및 Table ID에 기재된 모든 외부 키의 Object ID가 포함되어 있습니다.ID가 일정하게 유지되므로 스키마 및 테이블에서 추가 수정 시 신뢰성이 높아집니다.
쿼리:
SELECT
OBJECT_NAME(fkeys.constraint_object_id) foreign_key_name
,OBJECT_NAME(fkeys.parent_object_id) referencing_table_name
,COL_NAME(fkeys.parent_object_id, fkeys.parent_column_id) referencing_column_name
,OBJECT_SCHEMA_NAME(fkeys.parent_object_id) referencing_schema_name
,OBJECT_NAME (fkeys.referenced_object_id) referenced_table_name
,COL_NAME(fkeys.referenced_object_id, fkeys.referenced_column_id)
referenced_column_name
,OBJECT_SCHEMA_NAME(fkeys.referenced_object_id) referenced_schema_name
FROM sys.foreign_key_columns AS fkeys
'where'를 사용하여 필터를 추가할 수도 있습니다.
WHERE OBJECT_NAME(fkeys.parent_object_id) = 'table_name' AND
OBJECT_SCHEMA_NAME(fkeys.parent_object_id) = 'schema_name'
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), sysconv(smallint,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
SELECT
object_name(parent_object_id),
object_name(referenced_object_id),
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
SQL Server에서 지정된 테이블을 참조하는 모든 외부 키 목록:
다음 쿼리를 통해 참조 테이블 이름과 열 이름을 얻을 수 있습니다...
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) = 'TableName'
그리고 다음 스크린샷은 이해해주세요.
이 스크립트를 사용하여 외부 키와 관련된 모든 세부 정보를 찾습니다.저는 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
첫번째
EXEC sp_fkeys 'Table', 'Schema'
그런 다음 Nimble을 사용합니다.결과를 재생하는 텍스트
위의 몇 가지 좋은 답변입니다.하지만 나는 하나의 질문으로 답을 얻는 것을 선호한다.이 코드는 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.계정: FK_Account_Customer
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'
이 선택을 스크립트의 생성 기준으로 사용하여는 관련 테이블에서 필요한 작업을 수행합니다.
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는 clause else tablename .tablename
@BankZ가 가장 선호하는 답변
sp_help 'TableName'
다른 스키마에 대해 추가
sp_help 'schemaName.TableName'
에 Mysql이 .information_schema.REFERENTIAL_CONSTRAINTS테이블 참고: 테이블 이름 또는 참조된 테이블 이름으로 필터링할 수 있습니다.
@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;
Which 테이블, 열 및 외부 키 이름을 모두 하나의 쿼리에 표시합니다.
데이터베이스의 모든 테이블에 대한 기본 키 및 고유 키 결정...
여기에는 모든 제약조건이 나열되며 마지막에 필터를 넣을 수 있습니다.
/* 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를 참조해 주십시오.
저는 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
다음은 SQL Server 2016에서 이 시나리오를 해결하기 위한 베스트 프랙티스입니다.
다음 명령을 사용하여 외부 키를 나열해야 합니다.
EXEC sp_fkeys 'TableName'
거기서 FK의 전체 정보를 볼 수 있다.FKTABLE_NAME, FKCOLUMN_NAME, FK_NAME, UPDATE_RULE, DELETE_RULE 컬럼은 외부 키를 삭제하고 잘라낸 후 다시 구현해야 하는 정보입니다.
스크립트는 다음과 같이 구성할 수 있습니다.
-- EXEC sp_fkeys 'TableName'
-- DROP CONSTRAINTS: I drop one, here drop every constraint you desire.
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName
DROP CONSTRAINT IF EXISTS FK_TableName_OtherTable
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
-- TRUNCATE
BEGIN TRANSACTION
TRUNCATE TABLE TableName
GO
COMMIT
-- RECREATE CONSTRAINTS: I recreate 1, here recreate every fk you desire
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TableName SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.TableName ADD CONSTRAINT
FK_TableName_OtherTable FOREIGN KEY
(
Id_FK
) REFERENCES dbo.OtherTable
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
** UPDATE_RULE 및 DELETE_RULE 값은 sp_fkeys 문서에서 확인할 수 있습니다.
그러면 선택한 테이블과 관련된 외부 키가 모두 취득됩니다.* _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]'
선택한 ID에 대한 모든 책임을 계산하는 방법이 있습니다.@dbTableName 값, @dbRowId 값 및 유형을 변경하기만 하면 됩니다(이 경우 82행의 " " 를 삭제할 필요가 있습니다).SET @SQL = ..)을 클릭합니다.즐거운 시간 되세요.
DECLARE @dbTableName varchar(max) = 'User'
DECLARE @dbRowId uniqueidentifier = '21d34ecd-c1fd-11e2-8545-002219a42e1c'
DECLARE @FK_ROWCOUNT int
DECLARE @SQL nvarchar(max)
DECLARE @PKTABLE_QUALIFIER sysname
DECLARE @PKTABLE_OWNER sysname
DECLARE @PKTABLE_NAME sysname
DECLARE @PKCOLUMN_NAME sysname
DECLARE @FKTABLE_QUALIFIER sysname
DECLARE @FKTABLE_OWNER sysname
DECLARE @FKTABLE_NAME sysname
DECLARE @FKCOLUMN_NAME sysname
DECLARE @UPDATE_RULE smallint
DECLARE @DELETE_RULE smallint
DECLARE @FK_NAME sysname
DECLARE @PK_NAME sysname
DECLARE @DEFERRABILITY sysname
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1;
CREATE TABLE #Temp1 (
PKTABLE_QUALIFIER sysname,
PKTABLE_OWNER sysname,
PKTABLE_NAME sysname,
PKCOLUMN_NAME sysname,
FKTABLE_QUALIFIER sysname,
FKTABLE_OWNER sysname,
FKTABLE_NAME sysname,
FKCOLUMN_NAME sysname,
UPDATE_RULE smallint,
DELETE_RULE smallint,
FK_NAME sysname,
PK_NAME sysname,
DEFERRABILITY sysname,
FK_ROWCOUNT int
);
DECLARE FK_Counter_Cursor CURSOR FOR
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), sysconv(smallint,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 = @dbTableName
OPEN FK_Counter_Cursor;
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT @dbCountOut = COUNT(*) FROM [' + @FKTABLE_NAME + '] WHERE [' + @FKCOLUMN_NAME + '] = ''' + CAST(@dbRowId AS varchar(max)) + '''';
EXECUTE sp_executesql @SQL, N'@dbCountOut int OUTPUT', @dbCountOut = @FK_ROWCOUNT OUTPUT;
INSERT INTO #Temp1 (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY, FK_ROWCOUNT) VALUES (@FKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY, @FK_ROWCOUNT)
FETCH NEXT FROM FK_Counter_Cursor INTO @PKTABLE_QUALIFIER, @PKTABLE_OWNER, @PKTABLE_NAME, @PKCOLUMN_NAME, @FKTABLE_QUALIFIER, @FKTABLE_OWNER, @FKTABLE_NAME, @FKCOLUMN_NAME, @UPDATE_RULE, @DELETE_RULE, @FK_NAME, @PK_NAME, @DEFERRABILITY;
END;
CLOSE FK_Counter_Cursor;
DEALLOCATE FK_Counter_Cursor;
GO
SELECT * FROM #Temp1
GO
다음과 같은 솔루션이 도움이 됩니다.
--Eliminar las llaves foraneas
declare @query varchar(8000)
declare cursorRecorrerTabla cursor for
SELECT 'ALTER TABLE [PoaComFinH].['+sch.name+'].['+referencingTable.Name+'] DROP CONSTRAINT ['+foreignKey.name+']' 'query'
FROM PoaComFinH.sys.foreign_key_columns fk
JOIN PoaComFinH.sys.tables referencingTable ON fk.parent_object_id = referencingTable.object_id
JOIN PoaComFinH.sys.schemas sch ON referencingTable.schema_id = sch.schema_id
JOIN PoaComFinH.sys.objects foreignKey ON foreignKey.object_id = fk.constraint_object_id
JOIN PoaComFinH.sys.tables referencedTable ON fk.referenced_object_id = referencedTable.object_id
--3ro. abrir el cursor.
open cursorRecorrerTabla
fetch next from cursorRecorrerTabla
into @query
while @@fetch_status = 0
begin
--inicio cuerpo del cursor
print @query
exec(@query)
--fin cuerpo del cursor
fetch next from cursorRecorrerTabla
into @query
end
--cerrar cursor
close cursorRecorrerTabla
deallocate cursorRecorrerTabla
아래 쿼리를 통해 확인할 수 있습니다.
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)
그것도 한번 해봐.
EXEC sp_fkeys 'tableName', 'schemaName'
와 함께sp_fkeyspk 테이블 이름과 스키마뿐만 아니라 fk 테이블 이름과 스키마로도 결과를 필터링할 수 있습니다.링크
with tab_list as (
select t.name AS Table_Name, t.object_id, s.name AS Table_Schema from sys.tables t, sys.schemas s
where t.schema_id = s.schema_id
and s.name = 'your schema')
select IIF(col.column_id = 1, tab.TABLE_SCHEMA + '.' + tab.TABLE_NAME, NULL) Table_Name,
col.Name AS Column_Name, IIF(col.IS_NULLABLE= 0, 'NOT NULL', '') Nullable, st.name Type,
CASE WHEN st.name = 'decimal' THEN CONVERT(NVARCHAR(4000), col.Precision) + ',' + CONVERT(NVARCHAR(4000), col.Scale)
WHEN col.max_length = -1 THEN 'max'
WHEN st.name in ('int', 'bit', 'bigint', 'datetime2') THEN NULL
ELSE CONVERT(NVARCHAR(4000), col.max_length / 2)
END
AS Length,
ss.name + '.' + stab.name Referenced_Table, scol.name Referenced_Column
from sys.COLUMNS col
INNER JOIN tab_list tab ON col.object_id = tab.object_id
INNER JOIN sys.types st ON col.system_type_id = st.system_type_id AND col.user_type_id = st.user_type_id
LEFT JOIN [sys].[foreign_key_columns] sfkc ON col.object_id = sfkc.parent_object_id AND col.column_id = sfkc.parent_column_id
LEFT JOIN sys.tables stab ON sfkc.referenced_object_id = stab.object_id
LEFT JOIN sys.columns scol ON sfkc.referenced_object_id = scol.object_id AND sfkc.referenced_column_id = scol.column_id
LEFT JOIN sys.schemas ss ON ss.schema_id = stab.schema_id
답장이 늦다는 것은 알지만, 모든 것을 쉽게 찾을 수 있는 방법이 있습니다.foreign_key_references다음은 해결책입니다.
솔루션 01:
EXEC SP_FKEYS 'MyTableName'; // It'll show you the all the information(in multiple tables) regarding to the TableName with all ForeignKey_References.
솔루션 02:
EXEC SP_HELP 'MyTableName'; // It'll show all ForeignKey references in a single table.
솔루션 03:
// It'll show you the Column_Name with Referenced_Table_Name
SELECT
COL_NAME(fc.parent_object_id,fc.parent_column_id) Column_Name,
OBJECT_NAME(f.parent_object_id) Table_Name
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) = 'MyTableName'
이것이 당신에게 많은 도움이 되기를 바랍니다. ;-)
이 답변의 형식은 다음과 같습니다.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;
언급URL : https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
반응형
'programing' 카테고리의 다른 글
| "Foundation_export" vs "extern" (0) | 2023.04.12 |
|---|---|
| WPF에서의 버튼 플랫스타일 설정 (0) | 2023.04.12 |
| SQL Server 텍스트 유형 대 varchar 데이터 유형 (0) | 2023.04.07 |
| SQL Server Management Studio에서 테이블 편집 후 변경 사항 저장 (0) | 2023.04.07 |
| SQL Server의 CASE 문에서는 OR이 지원되지 않습니다. (0) | 2023.04.07 |