Know relationships between all the tables of database in SQL Server
SqlSql ServerDatabaseTsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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.....
Solution 2 - Sql
Or you can look at schemacrawler
Solution 3 - Sql
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.
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')
Solution 4 - Sql
This stored procedure will provide you with a hierarchical tree of relationship. Based on this article 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
Solution 5 - Sql
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 http://office.microsoft.com/en-us/visio-help/reverse-engineering-an-existing-database-HA001182257.aspx
Solution 6 - Sql
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)
Solution 7 - Sql
If you have LINQPad (it's free), this script I just wrote will list every possible path between every table in your database.
Given the following database:
...the script will produce the following output:
Or you can set the longestOnly flag at the top of the script, and it will just output the longest paths:
And here's the script:
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.
Solution 8 - Sql
select * from information_schema.REFERENTIAL_CONSTRAINTS where
UNIQUE_CONSTRAINT_SCHEMA = 'SCHEMA_NAME'
This will list the constraints with SCHEMA_NAMEenter image description here