반응형
SQL Server에서 주어진 테이블을 참조하는 모든 외래 키를 나열하려면 어떻게해야합니까?
SQL Server 데이터베이스에서 고도로 참조되는 테이블을 제거해야합니다. 테이블을 삭제하기 위해 제거해야하는 모든 외래 키 제약 조건의 목록을 어떻게 얻을 수 있습니까?
(SQL은 관리 스튜디오의 GUI에서 정보를 클릭하는 것보다 선호합니다.)
아무도 제안하지 않은 이유는 모르지만 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에서 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')
)
이것은 당신에게 제공합니다 :
- FK 자체
- FK가 속한 스키마
- " 참조 테이블 "또는 FK가있는 테이블
- " 참조 열 "또는 FK를 가리키는 참조 테이블 내부의 열
- " 참조 된 테이블 "또는 FK가 가리키는 키 열이있는 테이블
- " referenced column "또는 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'
다른 개체에 대한 참조도 염두에 두어야합니다.
테이블이 뷰, 저장 프로 시저, 함수 등과 같은 다른 개체에서도 많이 참조되는 것보다 다른 테이블에서 많이 참조 된 경우.
SQL로만 수행하려는 경우 다른 개체에서 종속성을 검색하면 오류가 발생할 수 있으므로 SSMS의 '종속성보기'대화 상자 또는 ApexSQL 검색 과 같은 무료 도구와 같은 GUI 도구를 권장합니다 .
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 절을 생략하십시오.
다음은 내가 사용할 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])
이 모든 명령을 사용하면 관련 외래 키를 삭제하는 데 필요한 모든 것이 있으므로 테이블을 삭제 한 다음 나중에 다시 만들 수 있습니다.
휴. 도움이 되었기를 바랍니다.
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
가장 간단한 방법은 SQL에서 sys.foreign_keys_columns를 사용하는 것입니다. 여기서 테이블에는 참조 열 및 테이블뿐만 아니라 참조 열 ID 참조 테이블 ID에 대한 모든 외래 키의 개체 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
object_name(parent_object_id),
object_name(referenced_object_id),
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Table Name')
이 스크립트를 사용하여 외래 키와 관련된 모든 세부 정보를 찾습니다. 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'
그런 다음 NimbleText 를 사용 하여 결과를 재생하십시오.
위의 몇 가지 좋은 답변. 그러나 나는 하나의 쿼리로 답을 얻는 것을 선호 합니다. 이 코드는 sys.sp_helpconstraint (sys proc)에서 가져옵니다.
이것이 Microsoft가 tbl에 연결된 외래 키가 있는지 찾는 방법입니다.
--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
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
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;
테이블, 열 및 외래 키 이름을 모두 하나의 쿼리에 표시합니다.
데이터베이스의 모든 테이블에 대한 기본 키 및 고유 키 결정 ...
이것은 모든 제약 조건을 나열하고 마지막에 필터를 넣을 수 있습니다
/* 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
선택한 Id에 대한 모든 책임의 수를 얻는 방법이 있습니다. @dbTableName 값, @dbRowId 값 및 유형을 변경하기 만하면됩니다 (int 인 경우 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
Mysql 서버에는 information_schema.REFERENTIAL_CONSTRAINTS테이블 FYI 가 있으며 테이블 이름 또는 참조 된 테이블 이름으로 필터링 할 수 있습니다.
선택한 테이블과 관련된 모든 외래 키를 가져옵니다. * _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]'
다음 솔루션이 저에게 효과적입니다.
--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_fkeys당신 PK 테이블 이름과 스키마뿐만 아니라 FK 테이블 이름과 스키마뿐만 아니라하여 결과를 필터링 할 수 있습니다. 링크
반응형
'your programing' 카테고리의 다른 글
| Scala 대 Groovy 대 Clojure (0) | 2020.09.30 |
|---|---|
| 쉘 스크립트에서 하나의 하위 문자열을 다른 문자열로 교체 (0) | 2020.09.30 |
| 애플리케이션 또는 프로세스의 실제 메모리 사용량을 측정하는 방법은 무엇입니까? (0) | 2020.09.30 |
| AddTransient, AddScoped 및 AddSingleton 서비스 차이점? (0) | 2020.09.30 |
| vim에서 DOS 줄 끝을 Linux 줄 끝으로 변환 (0) | 2020.09.30 |