{"id":89858,"date":"2021-02-09T00:48:44","date_gmt":"2021-02-09T00:48:44","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=89858"},"modified":"2026-03-09T12:22:17","modified_gmt":"2026-03-09T12:22:17","slug":"visualizing-a-set-of-sql-server-graph-tables-by-generating-tgf-format","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/visualizing-a-set-of-sql-server-graph-tables-by-generating-tgf-format\/","title":{"rendered":"Visualize SQL Server Graph Tables: Generate TGF Format"},"content":{"rendered":"\n

This article provides a reusable PowerShell<\/a> and dynamic SQL solution for visualizing SQL Server<\/a> graph tables. It exports node and edge data to Trivial Graph Format (TGF), a simple text-based format that graph visualization tools like yEd can render as interactive diagrams. The code uses the JSON metadata stored in SQL Server\u2019s $node_id, $from_id, and $to_id columns to map relationships dynamically – you specify which node and edge tables to include, and the tool generates the graph. This works with any SQL Server 2017+ graph database.<\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n

In my last blog<\/a>, I built some PowerShell to take a graph that I had modelled and bring that into SQL Server in a format that could be imported into SQL Server node and edge tables. I have started on a tool to take that imported data and create a set of graph tables to test with, but to test that process, I found it kind of important to be able to visualize a graph to make sure it worked!<\/p>\n\n\n\n

I considered using a rich format like Arvind Shyamsundar did in his blog <\/a>by outputting GraphML, but that was kind of overkill since every time I import a set of nodes, I will have to reformat anyhow. It turned out to be much easier to build generic code using TGF (Trivial Graph Format) just like in my export example.<\/p>\n\n\n\n

Another goal of mine is to make this tool to work with any graph in SQL Server (I am currently building an some code to do just that to categorize geographies, so this is a double win) so I used dynamic SQL as the basis, so I could pick and choose the node and edge tables, plus dynamically set a column for the nodes to display. (A future enhancement will be to include a filter on the node value so you can just see how a few nodes are related.)<\/p>\n\n\n\n

I did some searching for some sample graph data in SQL Server objects, and I found this example on the docs.microsoft.com site<\/a>. It contains a complete database with 3 nodes and 3 edges that gave me something to output. This code should work on 2017 or 2019 equally well.<\/p>\n\n\n\n

It is a quite a bit of code, none of it super interesting for including directly in the blog, except the base of the code using the JSON metadata values that is a part of the structure of the SQL Server objects. Since an edge can connect any number of nodes, you use these values to know what you can join to and from:<\/p>\n\n\n\n

SELECT  JSON_VALUE(CAST($node_id AS nvarchar(1000)),'$.schema') AS NodeSchema, \n\t\tJSON_VALUE(CAST($node_id AS nvarchar(1000)),'$.table') AS NodeTable, \n\t\tJSON_VALUE(CAST($node_id AS nvarchar(1000)),'$.id') AS NodeId\nFROM   dbo.Person; --A node\n\nSELECT \n\t\tJSON_VALUE(CAST($edge_id AS nvarchar(1000)),'$.schema') AS EdgeSchema, \n\t\tJSON_VALUE(CAST($edge_id AS nvarchar(1000)),'$.table') AS EdgeTable, \n\t\tJSON_VALUE(CAST($edge_id AS nvarchar(1000)),'$.id') AS EdgeId, \n\t\t\n\t\tJSON_VALUE(CAST($from_id AS nvarchar(1000)),'$.schema') AS FromNodeSchema, \n\t\tJSON_VALUE(CAST($from_id AS nvarchar(1000)),'$.table') AS FromNodeTable, \n\t\tJSON_VALUE(CAST($from_id AS nvarchar(1000)),'$.id') AS FromNodeId, \n\t\tJSON_VALUE(CAST($To_id AS nvarchar(1000)),'$.schema') AS ToNodeSchema, \n\t\tJSON_VALUE(CAST($to_id AS nvarchar(1000)),'$.table') AS ToNodeTable, \n\t\tJSON_VALUE(CAST($to_id AS nvarchar(1000)),'$.id') AS ToNodeId\nFROM  dbo.friendOf; --An edge<\/pre>\n\n\n\n
The output of this query is:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Each node object has its own surrogate key values that start at 0, so if you are going to use the code for more than one node at a time, you have to make the surrogate values unique for the TGF file (see the last blog<\/a> on importing for more details on that). In the code I make a temp table to stage the objects, so if you have > 1 node, the second set of keys need to start off where the previous ones left off. So the code uses an identity column, and joins to that identity column by schema, table, and edgeId, outputting the unique key:<\/p>\n\n\n\n
The code has a few things to choose in the header. The database, a list of nodes and edges you want in the output (for example, you might wish to graph just persons through the friendOf edge (example later)). There are a few default node and edge type values that control if matching labels or node types are appended to the graph.<\/p>\n\n\n\n
USE GraphDemo\nGO\n\n--parameters values for this database: https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/graphs\/sql-graph-sample?view=sql-server-ver15\n--list of nodes in format schema.nodetable.nameForLabel;schema.nodetable.nameForLabel;\n--done this way because it is a lot easier to manually edit\nDECLARE @NodeList nvarchar(4000) = 'dbo.person.name;dbo.Restaurant.name;dbo.City.name';\n\n--list of edges in format schema.edgeTable\nDECLARE @EdgeList nvarchar(4000) = 'dbo.likes;dbo.friendOf;dbo.locatedIn;dbo.livesIn';\n\n--used to determine formatting of name in output\nDECLARE @DefaultNodeType nvarchar(100) = '?'; --I want them all to have the node type appended in parens\nDECLARE @DefaultEdgeType nvarchar(100) = '?';\nDECLARE @LabelNonDefaultEdgeFlag bit = 1;<\/pre>\n\n\n\n
You can find the actual code here from my github repository: https:\/\/github.com\/drsqlgithub\/TGFImport\/blob\/main\/OutputToTGF.sql<\/a>.<\/p>\n\n\n\n
Using the parameters above (and the ones that are in the file), the output of my code from the database from the Microsoft demo script is:<\/p>\n\n\n\n
outputValue\n-----------------------------------\n1 Bellevue (City)\n2 Seattle (City)\n3 Redmond (City)\n4 John (person)\n5 Mary (person)\n6 Alice (person)\n7 Jacob (person)\n8 Julie (person)\n9 Taco Dell (Restaurant)\n10 Ginger and Spice (Restaurant)\n11 Noodle Land (Restaurant)\n#\n4 5 friendOf\n5 6 friendOf\n6 4 friendOf\n7 5 friendOf\n8 7 friendOf\n4 9 likes\n5 10 likes\n6 11 likes\n7 11 likes\n8 11 likes\n4 1 livesIn\n5 2 livesIn\n6 3 livesIn\n7 3 livesIn\n8 1 livesIn\n9 1 locatedIn\n10 2 locatedIn\n11 3 locatedIn<\/pre>\n\n\n\n
I chose to label all of the node types, and all of the edges. Saving this output (without the header) as a text file with a TGF extension, you can open and then importing into yEd<\/a> (a free tool), you see this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
TGF files have no formatting (and how would you include formatting from a set of SQL Server objects anyhow), so you need to use the tools to format the diagram. There are two steps to make a good looking diagram reasonably quick.<\/p>\n\n\n\n
Use the Tools\\Fit Node to Label<\/strong> menu. This will make the nodes sized so that the text is not hanging over the edge.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Then use one of tools in the Layout<\/strong> menu to output your diagram. I find the radial format works pretty good for semi complex, highly connected data. Keep in mind that any model is going to get complex to render (or even hand draw) fast. Radial<\/strong> put out a model that looks like:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
If you only want a single node and edge, you can just set a couple of nodes, like here the subgraph of person and friendOf:<\/p>\n\n\n\n
DECLARE @NodeList nvarchar(4000) = 'dbo.person.name';\nDECLARE @EdgeList nvarchar(4000) = 'dbo.friendOf';<\/pre>\n\n\n\n
The output is lets you see the following subgraph showing you who is friends with whom without the other noise::<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
It does kind of make me think about the name “friendOf”. Is friendOf a good name for a relationship that can be onesided? Not sure, but I did compare the data and this query:<\/p>\n\n\n\n
SELECT Person.Name, 'FriendOf', Friend.Name AS FriendName\nFROM Person, dbo.friendOf, Person AS Friend\nWHERE MATCH(Person - (friendOf) -> Friend);<\/pre>\n\n\n\n
Outputs the following:<\/p>\n\n\n\n
Name               FriendName\n--------- -------- --------------\nJohn      FriendOf Mary\nMary      FriendOf Alice\nAlice     FriendOf John\nJacob     FriendOf Mary\nJulie     FriendOf Jacob<\/pre>\n\n\n\n
Or you might want to see person and city, with just the edges friendOf and livesIn. Note that you will only see edges that go from person to person, city to city, or city to person (or person to city):<\/p>\n\n\n\n
DECLARE @NodeList nvarchar(4000) = 'dbo.person.name;dbo.city.name';\nDECLARE @EdgeList nvarchar(4000) = 'dbo.friendOf;dbo.livesIn';<\/pre>\n\n\n\n
This outputs the following graph diagram:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Hopefully this will help you if you are trying to draw up some sample data and you want to check to see the relationships graphically.<\/p>\n\n\n\n
Read also:<\/strong>
SQL Server Graph Databases introduction<\/a>
JSON handling in SQL Server<\/a>
UML database diagrams for SQL Server<\/a><\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: SQL Server graph tables<\/h2>\n\n                        
1. How do you visualize SQL Server graph tables?<\/h3>\n            
\n                
Export graph data to a visualization format like TGF (Trivial Graph Format) or GraphML, then open the file in a graph visualization tool like yEd, Gephi, or Cytoscape. SQL Server doesn\u2019t include built-in graph visualization – SSMS shows graph tables as regular tables. To export, query the $node_id and $from_id\/$to_id JSON metadata from node and edge tables to build the graph structure programmatically.<\/p>\n            <\/div>\n                    
2. What is TGF format and how does it work with SQL Server graphs?<\/h3>\n            
\n                
TGF (Trivial Graph Format) is a simple text format: each line is either a node (ID followed by label) or an edge (from-ID followed by to-ID). A # separator divides the node section from the edge section. It\u2019s easy to generate from SQL queries and can be opened in yEd Graph Editor, which renders the graph with automatic layout algorithms.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Visualize SQL Server graph tables by generating TGF (Trivial Graph Format). Uses PowerShell and dynamic SQL to export node\/edge relationships for display in yEd or other graph tools.…<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[6072,136366,4151],"coauthors":[19684],"class_list":["post-89858","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-graph","tag-sql-graph","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89858","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89858"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89858\/revisions"}],"predecessor-version":[{"id":109095,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89858\/revisions\/109095"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89858"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89858"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}