generate draw io ERD of SEDE

0

Please login or register to vote for this query.

(click on this box to dismiss)

a little bit work in progress but it does output an xml that can be imported into draw.io

Ask Different

Q&A for power users of Apple hardware and software

create table #pos ( name sysname
                  , x int
				  , y int
				  , cnt_exit int default 0
                  , cnt_entry int default 0);
GO
-- this tries to get the tables in an inital ok-ish position
insert into #pos(name, x, y) values ('PostTypes',1000,10);
insert into #pos(name, x, y) values ('Posts',1000,100);
insert into #pos(name, x, y) values ('PostHistory',1500,100);
insert into #pos(name, x, y) values ('PostHistoryTypes',2000,100);
insert into #pos(name, x, y) values ('PostFeedback',500,100);
insert into #pos(name, x, y) values ('PostLinks',500,300);
insert into #pos(name, x, y) values ('PendingFlags',1500,300);
insert into #pos(name, x, y) values ('FlagTypes',2000,500);
insert into #pos(name, x, y) values ('PostTags',500,600);
insert into #pos(name, x, y) values ('Tags',500,800);
insert into #pos(name, x, y) values ('TagSynonyms',500,1000);
insert into #pos(name, x, y) values ('Votes',1000,800);
insert into #pos(name, x, y) values ('VoteTypes',1000,1000);
insert into #pos(name, x, y) values ('Comments',1500,1500);
insert into #pos(name, x, y) values ('Users',2100,100);
insert into #pos(name, x, y) values ('Badges',2800,100);
insert into #pos(name, x, y) values ('SuggestedEdits',1200,100);
insert into #pos(name, x, y) values ('SuggestedEditVotes',1300,100);
insert into #pos(name, x, y) values ('CloseReasonTypes',100,700);
insert into #pos(name, x, y) values ('CloseAsOffTopicReasonTypes',100,800);
insert into #pos(name, x, y) values ('ReviewTasks',100,900);
insert into #pos(name, x, y) values ('ReviewTaskTypes',200,300);
insert into #pos(name, x, y) values ('ReviewTaskStates',200,500);
insert into #pos(name, x, y) values ('ReviewTaskResults',200,700);
insert into #pos(name, x, y) values ('ReviewTaskResultTypes',200,900);
insert into #pos(name, x, y) values ('ReviewRejectionReasons',400,400);
 
-- map a counter to an x and y value to be used as exit or entry point
-- when a relation exists
select id, x, y 
into #map 
from (
select 0 as id, 0 as x, 0 as y
union
select 1, 0.25, 0
union
select 2, 0.50, 0
union
select 3, 0.75, 0
union
select 4, 1, 0
union
select 5, 0, 0.25
union
select 6, 0, 0.50
union
select 7, 0, 0.75
union
select 8, 0, 1
union
select 9, 0.25, 1
union
select 10, 0.50, 1
union
select 11, 0.75, 1
union
select 12, 1, 1
union
select 13, 1, 1
union
select 14, 1, 1
) as datainout

-- create attribute
-- handle all attributes for ONE object
IF OBJECT_ID('#attributes', 'P') IS NOT NULL
	DROP PROCEDURE #attributes;
GO
CREATE PROCEDURE #attributes 
      @oid INT
	, @result NVARCHAR(max) OUT
AS
BEGIN
	declare @ending nvarchar(max)
	declare @cid int
	declare @name nvarchar(50)
	declare @width int
	declare @height int

	declare c_col cursor for select c.column_id
						  , c.name
						  , (select max(len(name)) 
							from sys.columns ml
							where ml.object_id = c.object_id
							group by ml.object_id) as width
						  , (select max(column_id)
							from sys.columns ml
							where ml.object_id = c.object_id
							group by ml.object_id) as height
					 from sys.columns c
					 inner join sys.tables t on t.object_id = c.object_id
                     where t.object_id = @oid
	open c_col
	fetch next from c_col into @cid, @name, @width, @height
    -- the id doesn't matter as long as it is uniue
    -- notice the parent attribute DOES require an existing id!
	set @result = '
		<mxCell id="'+ cast(floor(@oid / 10000) as nvarchar)+ '" value="'
	set @ending = '" 
				style="whiteSpace=wrap;html=1;align=left;strokeColor=none;fillColor=none;spacingLeft=4;fontSize=17;verticalAlign=top;resizable=0;rotatable=0;part=1;" 
				vertex="1" 
				parent="'+ cast(@oid as nvarchar) +'">
					<mxGeometry y="32" 
								width="' + cast(@width*16 as nvarchar) + '" 
								height="'+ cast(@height*16 as nvarchar) +'" 
								as="geometry"/>
		</mxCell>'
	while(@@FETCH_STATUS = 0)
	begin
		set @result = @result + '+' + @name + '&#xa;'
		fetch next from c_col into @cid, @name, @width , @height
	end
	set @result = left(@result, len(@result)-5) + @ending
	close c_col
	deallocate c_col

END;
GO

-- create entity
-- create xml for one single object
IF OBJECT_ID('#entity', 'P') IS NOT NULL
	DROP PROCEDURE #entity;
GO
CREATE PROCEDURE #entity 
      @oid INT
	, @res NVARCHAR(max) OUT
AS
BEGIN
    DECLARE @max int
	-- calculate a reasonable with (take lenght of columnames in account
    SELECT @max = MAX(IIF(len(t.name) > len(c.name), len(t.name), len(c.name)))
    FROM sys.tables t
	INNER JOIN sys.columns c on c.object_id = t.object_id
	WHERE t.object_id = @oid
	
    SELECT @res = '
	    <mxCell 
            style="swimlane;html=1;childLayout=stackLayout;horizontal=1;startSize=32;horizontalStack=0;fillColor=#008cff;fontColor=#FFFFFF;rounded=1;fontSize=17;fontStyle=0;strokeWidth=2;resizeParent=0;resizeLast=1;shadow=0;dashed=0;align=center;;swimlaneLine=0"
            id="'+ cast(@oid as nvarchar)+ '"
			parent="1" 
			vertex="1" 
			value="' + t.name + '">
				<mxGeometry 
					as="geometry" 
					height="' + cast(t.max_column_id_used * 24 + 32 as nvarchar) + '" 
					width="' + cast(@max * 10 as nvarchar) + '" 
					y="' + cast(p.y  as nvarchar)+ '" 
					x="' + cast(p.x  as nvarchar)+'"/>
		</mxCell>'
    FROM sys.tables t
	INNER JOIN #pos p on t.name = p.name
	WHERE t.object_id = @oid
END;
GO

-- create relation
-- finds the relation between an attribute in one table
-- and the primaty key of another based on naming scheme
IF OBJECT_ID('#relation', 'P') IS NOT NULL
	DROP PROCEDURE #relation;
GO
CREATE PROCEDURE #relation @oid INT
    , @baseId int
	, @result NVARCHAR(max) OUT
AS
BEGIN
	declare @src int
	declare @id int
	declare @sname nvarchar(50)
	declare @keepname nvarchar(50)
	declare @tgt int
    declare	@tname nvarchar(50)
    declare	@col nvarchar(50)
    declare @cnt_entry int
    declare @cnt_exit int
    declare @entryx decimal(5,2)
    declare @entryy decimal(5,2)
    declare @exitx decimal(5,2)
    declare @exity decimal(5,2)

    -- find relation tables and columns
	declare c_rel cursor for 
		select row_number() over(order by s.object_id asc) as id 
			, s.object_id as source
			, s.name as sn
			, t.object_id as target
			,t.name as tn
			, t.colname
		from sys.tables s
		inner join (
		   select t.object_id, t.name, tc.name as colname
		   from sys.tables t 
		   inner join sys.columns tc on tc.object_id = t.object_id
		   where tc.name like '%Id' 
		   or tc.name like '%UserId'
		   or tc.name like '%ModeratorId'
		   or tc.name in ('SourceTagName', 'TargetTagName')
		  ) as t on t.colname = left(s.name, len(s.name)-1)+'Id' 
		  or (t.colname in ('SourceTagName', 'TargetTagName') and s.name = 'Tags')
		  or (t.colname like '%UserId' and s.name = 'Users')
		  or (t.colname like '%ModeratorId' and s.name = 'Users')
		where s.object_id = @oid

	open c_rel
	fetch next from c_rel into @id, @src, @sname, @tgt, @tname, @col
	
    -- to prevent all relations start and end at the same point
    -- we keep track of the entries used per entity
	set @keepname = @sname
	select @cnt_entry=cnt_entry
 	from #pos 
	where name = @keepname
	
    print @keepname + ' ' + cast(@cnt_entry as nvarchar)
    
	set @result = ''
	while(@@FETCH_STATUS = 0)
	begin
	    -- find wich entry is next
	    select @cnt_exit=cnt_entry
 	    from #pos 
	    where name = @tname
        -- get the x and y
        select @entryx=x, @entryy=y
        from #map
        where id=@cnt_exit
       
        print @cnt_entry
        print @entryx
        print @entryy
        
        select @exitx=x, @exity=y
        from #map
        where id=@cnt_entry
        
        print @cnt_exit
        print @exitx
        print @exity
        
		set @result = @result + '
			<mxCell id="' + cast(@id + @baseId as nvarchar) + '" 
					value="' + @tname+'.'+ @col + '->' + @sname + '" 
                    style="edgeStyle=none;html=1;endArrow=ERmany;endFill=1;entryX='+cast(@entryx as nvarchar) +';entryY='+ cast(@entryy as nvarchar) +';entryPerimeter=0;exitX='+ cast(@exitx as nvarchar) +';exitY='+ cast(@exity as nvarchar) +';" 
					edge="1" 
					parent="1" 
					source="'+ cast(@src as nvarchar) +'" 
					target="'+ cast(@tgt as nvarchar) +'">
				<mxGeometry width="100" height="100" relative="1" as="geometry">
					<mxPoint x="310" y="400" as="sourcePoint"/>
					<mxPoint x="450" y="270" as="targetPoint"/>
				</mxGeometry>
			</mxCell>'
        set @cnt_exit = @cnt_exit + 1    
         
        print 'update exit ' + @tname + ' to ' + cast(@cnt_exit as nvarchar)     
        -- counting per table
        update #pos 
        set cnt_entry = @cnt_exit
        where name = @tname
        
        set @cnt_entry = @cnt_entry + 1
        
		fetch next from c_rel into @id, @src, @sname, @tgt, @tname, @col
	end
	close c_rel
	deallocate c_rel
    
    print 'update entry' + @keepname + ' to ' + cast(@cnt_entry as nvarchar)
    -- counting per table
	update #pos 
    set cnt_entry=@cnt_entry
	where name = @keepname
END;
GO

-- main loop

declare @oid int
declare @baseId int
declare @result nvarchar(max)
declare @onerow nvarchar(max)
declare @relations nvarchar(max)

declare c_tab cursor for select object_id
						 from sys.tables
                         -- surpress Types
                         where name not like '%Types'

-- we need unique ids for relations
set @baseId = 30
set @relations = ''

set @result='<mxGraphModel dx="835" dy="760" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" fold="1" page="1" pageScale="1" pageWidth="826" pageHeight="1169" style="default-style2" math="0">
	<root>
		<mxCell id="0"/>
		<mxCell id="1" parent="0"/>'						 
						 
open c_tab
fetch next from c_tab into @oid
while(@@FETCH_STATUS = 0)
begin
    exec #entity @oid, @onerow output
    set @result = @result + @onerow
	
	exec #attributes @oid, @onerow output
	set @result = @result + @onerow

    -- relations need to be at the end
    -- because if they use a tagetid
    -- that is not yet in the xml, draw.io breaks
	exec #relation @oid, @baseid, @onerow output
    -- store for later
	set @relations = @relations + @onerow
    -- no more than 30 relations per table
	set @baseId = @baseId + 30
	
    fetch next from c_tab into @oid
end
close c_tab
deallocate c_tab

-- add our relations near the end
set @result = @result + @relations + '
    </root>
</mxGraphModel>'

-- select * from #pos  -- for debugging
select @result

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results