Skip to content

Instantly share code, notes, and snippets.

@Ivajkin

Ivajkin/Create GraphViz from DB.sql

Created September 25, 2019 15:27
Show Gist options
  • Save Ivajkin/99a2226e912aeaf8025c28e917e57e38 to your computer and use it in GitHub Desktop.
Save Ivajkin/99a2226e912aeaf8025c28e917e57e38 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Create GraphViz from DB.sql
Declare cTables Cursor LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY For
SELECT Distinct
OBJECT_SCHEMA_NAME(fk.parent_object_id) AS Table1_Scheme_Name,
OBJECT_NAME(fk.parent_object_id) AS Table1_Name,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS Table2_Scheme_Name,
OBJECT_NAME (fk.referenced_object_id) AS Table2_Name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns AS fc ON fk.[OBJECT_ID] = fc.constraint_object_id
where fk.parent_object_id <> fk.referenced_object_id AND
OBJECT_SCHEMA_NAME(fk.parent_object_id) NOT IN ('sys', 'pm', 'alog', 'mnt') and
OBJECT_SCHEMA_NAME(fk.referenced_object_id) NOT IN ('sys', 'pm', 'alog', 'mnt') AND
OBJECT_NAME(fk.parent_object_id) NOT LIKE '%cube%' and
OBJECT_NAME(fk.referenced_object_id) NOT LIKE '%cube%';
Open cTables;
Declare @table1_Scheme_Name sysname,
@table1_Name sysname,
@table2_Scheme_Name sysname,
@table2_Name sysname;
While 1 = 1
Begin
Fetch Next From cTables Into @table1_Scheme_Name, @table1_Name, @table2_Scheme_Name, @table2_Name;
If @@FETCH_STATUS <> 0
Break;
PRINT Concat(@table1_Scheme_Name, '_', @table1_Name, ' -> ', @table2_Scheme_Name, '_', @table2_Name, ';');
End;
Close cTables;
Deallocate cTables;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment