Kenntnis der Beziehungen zwischen allen Tabellen der Datenbank in SQL Server
Ich möchte alle wissen, wie die Tabellen in meiner Datenbank miteinander verbunden sind (d. H. PK/FK/UK). Daher habe ich ein Datenbankdiagramm aller meiner Tabellen in SQL Server erstellt. Das erstellte Diagramm war nicht leicht lesbar und musste gescrollt werden (horizontal und manchmal vertikal), um die Tabelle am anderen Ende zu sehen.
Kurz gesagt, das Datenbankdiagramm von SQL ist nicht UI-freundlich, wenn es darum geht, die Beziehungen zwischen vielen Tabellen zu kennen.
Meine (einfache) Frage: Gibt es so etwas wie ein Datenbankdiagramm, das das tun kann, was DB-Diagramm getan hat, aber auf "gute" Weise?
Manchmal kann auch eine Textdarstellung hilfreich sein. Mit dieser Abfrage in den Systemkatalogsichten können Sie eine Liste aller FK-Beziehungen und der Verknüpfung der beiden Tabellen (und der Spalten, für die sie arbeiten) abrufen.
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
Wenn Sie dies in Excel ablegen, können Sie in Scheiben schneiden und Würfel schneiden - basierend auf der übergeordneten Tabelle, der referenzierten Tabelle oder irgendetwas anderem.
Ich finde visuelle Anleitungen hilfreich - aber manchmal ist Textdokumentation genauso gut (oder noch besser) - nur meine 2 Cent .....
Oder Sie können nach schemacrawler suchen
Eine weitere Möglichkeit, dieselben Daten mit INFORMATION_SCHEMA abzurufen
Die in SQL Server enthaltenen Informationsschemaansichten entsprechen der ISO-Standarddefinition für INFORMATION_SCHEMA.
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')
Microsoft Visio ist wahrscheinlich das Beste, das ich je gesehen habe, obwohl es meines Wissens nicht automatisch basierend auf Ihren Beziehungen generiert wird.
BEARBEITEN: Probieren Sie dies in Visio aus, geben Sie an, was Sie brauchen http://office.Microsoft.com/de-de/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx
Diese gespeicherte Prozedur bietet Ihnen eine hierarchische Beziehungsstruktur. Basierend auf diesem Artikel von Technet. Optional erhalten Sie auch eine Abfrage zum Lesen oder Löschen aller zugehörigen Daten.
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 [email protected]
AND soc.name [email protected]
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
select * from information_schema.REFERENTIAL_CONSTRAINTS where
UNIQUE_CONSTRAINT_SCHEMA = 'TABLE_NAME'
Dadurch wird die Spalte mit TABLE_NAME und REFERENCED_COLUMN_NAME aufgelistet.
Meine Lösung basiert auf der @ marc_s-Lösung. Ich habe nur Spalten verkettet, falls eine Einschränkung auf mehreren Spalten basiert:
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)