![]() |
Know relationships between all the tables of database in SQL Server - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +--- Thread: Know relationships between all the tables of database in SQL Server (/Thread-Know-relationships-between-all-the-tables-of-database-in-SQL-Server) |
Know relationships between all the tables of database in SQL Server - fideliawxwrkxvtxx - 07-20-2023 I wish to all know how the tables in my database are related to each other (i.e PK/FK/UK) and hence i created a database diagram of all my tables in SQL Server. The diagram that was created was not easily readable and had to scroll (horizontally and sometimes vertically) to see the table on the other end. In short SQL's db diagram are not UI friendly when it comes to knowing relationships between many tables. My (simple) Question: Is there something like database diagram which can do what db diagram did but in "good" way? RE: Know relationships between all the tables of database in SQL Server - oliadjqs - 07-20-2023 Sometimes, a textual representation might also help; with this query on the system catalog views, you can get a list of all FK relationships and how the link two tables (and what columns they operate on). 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 Dump this into Excel, and you can slice and dice - based on the parent table, the referenced table or anything else. I find visual guides helpful - but sometimes, textual documentation is just as good (or even better) - just my 2 cents..... RE: Know relationships between all the tables of database in SQL Server - Proelectroosmoses489 - 07-20-2023 Microsoft Visio is probably the best I've came across, although as far as I know it won't automatically generate based on your relationships. EDIT: try this in Visio, could give you what you need [To see links please register here] RE: Know relationships between all the tables of database in SQL Server - lookist542155 - 07-20-2023 Just another way to retrieve the same data using INFORMATION_SCHEMA > The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA. [sqlauthority way][1] 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') [1]: [To see links please register here] RE: Know relationships between all the tables of database in SQL Server - transitable576699 - 07-20-2023 This stored procedure will provide you with a hierarchical tree of relationship. Based on this [article][1] from Technet. It will also optionally provide you a query for reading or deleting all the related data. 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 [1]: [To see links please register here] RE: Know relationships between all the tables of database in SQL Server - hypersplenismzjxwugqcwm - 07-20-2023 My solution is based on @marc_s solution, i just concatenated columns in cases that a constraint is based on more than one column: 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) RE: Know relationships between all the tables of database in SQL Server - sarilda555 - 07-20-2023 Or you can look at [schemacrawler][1] [1]: [To see links please register here] RE: Know relationships between all the tables of database in SQL Server - afzaloqkepc - 07-20-2023 If you have [LINQPad][1] (it's free), this script I just wrote will list every possible path between every table in your database. Given the following database: [![enter image description here][2]][2] ...the script will produce the following output: [![enter image description here][3]][3] Or you can set the `longestOnly` flag at the top of the script, and it will just output the longest paths: [![enter image description here][4]][4] And here's the script: ```C# 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); } } } } ``` For a complex database, this can take a surprisingly long time to complete, and will return a surprisingly large list of results. I needed to write this for work, and the end result left me feeling pretty defeated. :) I couldn't find anything else that would do this, so I'm pretty happy with it, though. [1]: [To see links please register here] [2]:[3]: [4]: RE: Know relationships between all the tables of database in SQL Server - arvizu890 - 07-20-2023 select * from information_schema.REFERENTIAL_CONSTRAINTS where UNIQUE_CONSTRAINT_SCHEMA = 'SCHEMA_NAME' This will list the constraints with `SCHEMA_NAME`[enter image description here][1] [1]: RE: Know relationships between all the tables of database in SQL Server - margit278 - 07-20-2023 All suggestions thus far have shown relationships between entities via primary and foreign keys. Occasionally, it may be useful to also identify relationships via dependencies. I found the need for this to identify the relationships between views and tables when building network graph visualizations. 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; The above code results in the following table: [![SQL Table Result Screenshot][1]][1] This can be further extended, using python, to generate network graphs to visually see linkages. [![enter image description here][2]][2] [1]: [2]: |