SQL Server의 모든 데이터베이스 테이블 간의 관계 파악
데이터베이스의 테이블이 서로 어떻게 관련되어 있는지 알고 싶습니다(예: PK/FK/UK). 따라서 SQL Server의 모든 테이블에 대한 데이터베이스 다이어그램을 작성했습니다.작성된 다이어그램은 쉽게 읽을 수 없으며 다른 쪽 끝에 있는 표를 보려면 스크롤(수평 및 때로는 수직)해야 했습니다.
간단히 말해서 SQL의 db 다이어그램은 많은 테이블 간의 관계를 아는 것과 관련하여 UI 친화적이지 않습니다.
나의 (단순한) 질문:DB 다이어그램이 "좋은" 방법으로 수행할 수 있는 데이터베이스 다이어그램과 같은 것이 있습니까?
때로는 텍스트 표현도 도움이 될 수 있습니다. 시스템 카탈로그 보기에 대한 이 쿼리를 사용하면 모든 FK 관계의 목록과 두 테이블을 연결하는 방법(및 테이블이 작동하는 열)을 얻을 수 있습니다.
SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
tp.name, cp.column_id
이것을 Excel에 버리면 부모 테이블, 참조된 테이블 또는 다른 모든 것을 기준으로 슬라이스 및 주사위를 만들 수 있습니다.
저는 시각적 안내서가 도움이 된다고 생각합니다. 하지만 가끔은 텍스트 문서가 제 2센트만큼 좋습니다(또는 훨씬 더 좋습니다.
INFORMATION_SCHEMA를 사용하여 동일한 데이터를 검색하는 또 다른 방법입니다.
SQL Server에 포함된 정보 스키마 보기는 INFORMATION_SCHEMA에 대한 ISO 표준 정의를 준수합니다.
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')
또는 스키마 크롤러를 볼 수 있습니다.
이 저장 프로시저는 계층적 관계 트리를 제공합니다.테크넷의 이 기사를 기반으로 합니다.또한 모든 관련 데이터를 읽거나 삭제하기 위한 쿼리를 선택적으로 제공합니다.
IF OBJECT_ID('GetForeignKeyRelations','P') IS NOT NULL
DROP PROC GetForeignKeyRelations
GO
CREATE PROC GetForeignKeyRelations
@Schemaname Sysname = 'dbo'
,@Tablename Sysname
,@WhereClause NVARCHAR(2000) = ''
,@GenerateDeleteScripts bit = 0
,@GenerateSelectScripts bit = 0
AS
SET NOCOUNT ON
DECLARE @fkeytbl TABLE
(
ReferencingObjectid int NULL
,ReferencingSchemaname Sysname NULL
,ReferencingTablename Sysname NULL
,ReferencingColumnname Sysname NULL
,PrimarykeyObjectid int NULL
,PrimarykeySchemaname Sysname NULL
,PrimarykeyTablename Sysname NULL
,PrimarykeyColumnname Sysname NULL
,Hierarchy varchar(max) NULL
,level int NULL
,rnk varchar(max) NULL
,Processed bit default 0 NULL
);
WITH fkey (ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname
,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk)
AS
(
SELECT
soc.object_id
,scc.name
,soc.name
,convert(sysname,null)
,convert(int,null)
,convert(sysname,null)
,convert(sysname,null)
,convert(sysname,null)
,CONVERT(VARCHAR(MAX), scc.name + '.' + soc.name ) as Hierarchy
,0 as level
,rnk=convert(varchar(max),soc.object_id)
FROM SYS.objects soc
JOIN sys.schemas scc
ON soc.schema_id = scc.schema_id
WHERE scc.name =@Schemaname
AND soc.name =@Tablename
UNION ALL
SELECT sop.object_id
,scp.name
,sop.name
,socp.name
,soc.object_id
,scc.name
,soc.name
,socc.name
,CONVERT(VARCHAR(MAX), f.Hierarchy + ' --> ' + scp.name + '.' + sop.name ) as Hierarchy
,f.level+1 as level
,rnk=f.rnk + '-' + convert(varchar(max),sop.object_id)
FROM SYS.foreign_key_columns sfc
JOIN Sys.Objects sop
ON sfc.parent_object_id = sop.object_id
JOIN SYS.columns socp
ON socp.object_id = sop.object_id
AND socp.column_id = sfc.parent_column_id
JOIN sys.schemas scp
ON sop.schema_id = scp.schema_id
JOIN SYS.objects soc
ON sfc.referenced_object_id = soc.object_id
JOIN SYS.columns socc
ON socc.object_id = soc.object_id
AND socc.column_id = sfc.referenced_column_id
JOIN sys.schemas scc
ON soc.schema_id = scc.schema_id
JOIN fkey f
ON f.ReferencingObjectid = sfc.referenced_object_id
WHERE ISNULL(f.PrimarykeyObjectid,0) <> f.ReferencingObjectid
)
INSERT INTO @fkeytbl
(ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname
,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk)
SELECT ReferencingObjectid,ReferencingSchemaname,ReferencingTablename,ReferencingColumnname
,PrimarykeyObjectid,PrimarykeySchemaname,PrimarykeyTablename,PrimarykeyColumnname,Hierarchy,level,rnk
FROM fkey
SELECT F.Relationshiptree
FROM
(
SELECT DISTINCT Replicate('------',Level) + CASE LEVEL WHEN 0 THEN '' ELSE '>' END + ReferencingSchemaname + '.' + ReferencingTablename 'Relationshiptree'
,RNK
FROM @fkeytbl
) F
ORDER BY F.rnk ASC
-------------------------------------------------------------------------------------------------------------------------------
-- Generate the Delete / Select script
-------------------------------------------------------------------------------------------------------------------------------
DECLARE @Sql VARCHAR(MAX)
DECLARE @RnkSql VARCHAR(MAX)
DECLARE @Jointables TABLE
(
ID INT IDENTITY
,Object_id int
)
DECLARE @ProcessTablename SYSNAME
DECLARE @ProcessSchemaName SYSNAME
DECLARE @JoinConditionSQL VARCHAR(MAX)
DECLARE @Rnk VARCHAR(MAX)
DECLARE @OldTablename SYSNAME
IF @GenerateDeleteScripts = 1 or @GenerateSelectScripts = 1
BEGIN
WHILE EXISTS ( SELECT 1
FROM @fkeytbl
WHERE Processed = 0
AND level > 0 )
BEGIN
SELECT @ProcessTablename = ''
SELECT @Sql = ''
SELECT @JoinConditionSQL = ''
SELECT @OldTablename = ''
SELECT TOP 1 @ProcessTablename = ReferencingTablename
,@ProcessSchemaName = ReferencingSchemaname
,@Rnk = RNK
FROM @fkeytbl
WHERE Processed = 0
AND level > 0
ORDER BY level DESC
SELECT @RnkSql ='SELECT ' + REPLACE (@rnk,'-',' UNION ALL SELECT ')
DELETE FROM @Jointables
INSERT INTO @Jointables
EXEC(@RnkSql)
IF @GenerateDeleteScripts = 1
SELECT @Sql = 'DELETE [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10) + ' FROM [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10)
IF @GenerateSelectScripts = 1
SELECT @Sql = 'SELECT [' + @ProcessSchemaName + '].[' + @ProcessTablename + '].*' + CHAR(10) + ' FROM [' + @ProcessSchemaName + '].[' + @ProcessTablename + ']' + CHAR(10)
SELECT @JoinConditionSQL = @JoinConditionSQL
+ CASE
WHEN @OldTablename <> f.PrimarykeyTablename THEN 'JOIN [' + f.PrimarykeySchemaname + '].[' + f.PrimarykeyTablename + '] ' + CHAR(10) + ' ON '
ELSE ' AND '
END
+ ' [' + f.PrimarykeySchemaname + '].[' + f.PrimarykeyTablename + '].[' + f.PrimarykeyColumnname + '] = [' + f.ReferencingSchemaname + '].[' + f.ReferencingTablename + '].[' + f.ReferencingColumnname + ']' + CHAR(10)
, @OldTablename = CASE
WHEN @OldTablename <> f.PrimarykeyTablename THEN f.PrimarykeyTablename
ELSE @OldTablename
END
FROM @fkeytbl f
JOIN @Jointables j
ON f.Referencingobjectid = j.Object_id
WHERE charindex(f.rnk + '-',@Rnk + '-') <> 0
AND F.level > 0
ORDER BY J.ID DESC
SELECT @Sql = @Sql + @JoinConditionSQL
IF LTRIM(RTRIM(@WhereClause)) <> ''
SELECT @Sql = @Sql + ' WHERE (' + @WhereClause + ')'
PRINT @SQL
PRINT CHAR(10)
UPDATE @fkeytbl
SET Processed = 1
WHERE ReferencingTablename = @ProcessTablename
AND rnk = @Rnk
END
IF @GenerateDeleteScripts = 1
SELECT @Sql = 'DELETE FROM [' + @Schemaname + '].[' + @Tablename + ']'
IF @GenerateSelectScripts = 1
SELECT @Sql = 'SELECT * FROM [' + @Schemaname + '].[' + @Tablename + ']'
IF LTRIM(RTRIM(@WhereClause)) <> ''
SELECT @Sql = @Sql + ' WHERE ' + @WhereClause
PRINT @SQL
END
SET NOCOUNT OFF
go
Microsoft Visio는 두 분의 관계에 따라 자동으로 생성되지는 않지만, 제가 아는 한 제가 본 것 중에 최고일 것입니다.
편집: Visio에서 사용해 보십시오. http://office.microsoft.com/en-us/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx 에서 필요한 정보를 얻을 수 있습니다.
제 솔루션은 @marc_s 솔루션을 기반으로 합니다. 제약 조건이 둘 이상의 열을 기반으로 하는 경우 열을 연결했을 뿐입니다.
SELECT
FK.[name] AS ForeignKeyConstraintName
,SCHEMA_NAME(FT.schema_id) + '.' + FT.[name] AS ForeignTable
,STUFF(ForeignColumns.ForeignColumns, 1, 2, '') AS ForeignColumns
,SCHEMA_NAME(RT.schema_id) + '.' + RT.[name] AS ReferencedTable
,STUFF(ReferencedColumns.ReferencedColumns, 1, 2, '') AS ReferencedColumns
FROM
sys.foreign_keys FK
INNER JOIN sys.tables FT
ON FT.object_id = FK.parent_object_id
INNER JOIN sys.tables RT
ON RT.object_id = FK.referenced_object_id
CROSS APPLY
(
SELECT
', ' + iFC.[name] AS [text()]
FROM
sys.foreign_key_columns iFKC
INNER JOIN sys.columns iFC
ON iFC.object_id = iFKC.parent_object_id
AND iFC.column_id = iFKC.parent_column_id
WHERE
iFKC.constraint_object_id = FK.object_id
ORDER BY
iFC.[name]
FOR XML PATH('')
) ForeignColumns (ForeignColumns)
CROSS APPLY
(
SELECT
', ' + iRC.[name]AS [text()]
FROM
sys.foreign_key_columns iFKC
INNER JOIN sys.columns iRC
ON iRC.object_id = iFKC.referenced_object_id
AND iRC.column_id = iFKC.referenced_column_id
WHERE
iFKC.constraint_object_id = FK.object_id
ORDER BY
iRC.[name]
FOR XML PATH('')
) ReferencedColumns (ReferencedColumns)
LINQPad(무료)가 있는 경우, 방금 작성한 이 스크립트는 데이터베이스의 모든 테이블 사이에 가능한 모든 경로를 나열합니다.
다음 데이터베이스가 지정된 경우:
...이 스크립트는 다음과 같은 출력을 생성합니다.
또는 설정할 수 있습니다.longestOnly스크립트의 맨 위에 플래그를 지정하면 가장 긴 경로만 출력됩니다.
그리고 대본은 다음과 같습니다.
var longestOnly = true;
var pathLists = new List<List<string>>();
foreach (var table in Mapping.GetTables()) {
var subPaths = new List<string>();
pathLists.Add(subPaths);
subPaths.Add(table.TableName);
Go(table, subPaths);
}
var pathStrings = pathLists
.Select(p => string.Join(", ", p))
.Distinct()
.OrderBy(p => p)
.ToList();
if (longestOnly) {
pathStrings.RemoveAll(z => pathStrings.Any(i => i != z && i.Contains(z)));
} else {
pathStrings.RemoveAll(z => pathStrings.Any(i => i != z && i.StartsWith(z)));
}
pathStrings.Dump();
void Go(System.Data.Linq.Mapping.MetaTable table, List<string> paths)
{
foreach (var association in table.RowType.Associations) {
var subPaths = paths.Concat(new List<string>()).ToList(); // create a copy
pathLists.Add(subPaths);
var subPathTableName = association.OtherType.Table.TableName;
if (!subPaths.Contains(subPathTableName)) {
subPaths.Add(subPathTableName);
var subPathTable = Mapping.GetTable(association.OtherMember.DeclaringType.Type);
if (subPathTable != null) {
Go(subPathTable, subPaths);
}
}
}
}
복잡한 데이터베이스의 경우 이 작업을 완료하는 데 시간이 오래 걸릴 수 있으며 결과 목록이 놀라울 정도로 많습니다.저는 일 때문에 이것을 써야 했고, 결국 저는 패배감을 느꼈습니다.:)
저는 이것을 할 수 있는 다른 어떤 것도 찾을 수 없었기 때문에, 그래도 저는 그것에 대해 꽤 만족합니다.
지금까지의 모든 제안은 기본 키와 외부 키를 통해 엔티티 간의 관계를 보여주었습니다.때로는 종속성을 통해 관계를 식별하는 것도 유용할 수 있습니다.네트워크 그래프 시각화를 구축할 때 뷰와 테이블 간의 관계를 식별하기 위해 이 작업이 필요하다는 것을 알게 되었습니다.
select distinct
v.name as referencer_name,
V.type_desc as referencer_type,
o.name as referenced_entity_name,
o.type_desc as referenced_entity_type
from sys.views v
join sys.sql_expression_dependencies d
on d.referencing_id = v.object_id
and d.referenced_id is not null
join sys.objects o
on o.object_id = d.referenced_id
order by referencer_name;
위의 코드는 다음 표를 생성합니다.
이것은 파이썬을 사용하여 링크를 시각적으로 볼 수 있는 네트워크 그래프를 생성하도록 더 확장할 수 있습니다.
select * from information_schema.REFERENTIAL_CONSTRAINTS where
UNIQUE_CONSTRAINT_SCHEMA = 'SCHEMA_NAME'
다음과 같은 제약 조건이 나열됩니다.SCHEMA_NAME여기에 이미지 설명 입력
언급URL : https://stackoverflow.com/questions/8094156/know-relationships-between-all-the-tables-of-database-in-sql-server
'programing' 카테고리의 다른 글
| MySqlariaDB CREATE FUNCTION DELMITER가 작동하지 않습니다.근처에 오류가 있습니다. (0) | 2023.07.06 |
|---|---|
| Firestore 루트 컬렉션의 모든 문서 및 하위 컬렉션 가져오기 (0) | 2023.07.06 |
| 오라클 테이블에 자동으로 날짜 채우기 (0) | 2023.07.06 |
| 최신 버전의 gcc에서 c11을 활성화하는 방법은 무엇입니까? (0) | 2023.07.01 |
| sp_dbcmpt 수준이 더 이상 사용되지 않는 경우 SQL Server 데이터베이스 호환성을 확인하는 방법은 무엇입니까? (0) | 2023.07.01 |