Generating Data Dictionary for SQL Server with CatFactory

• Download AdventureWorks2012.DataDictionary.zip - 27.2 KB
• AdventureWorks database
• GitHub Repository

Introduction

CatFactory provides a mechanism for importing existing databases to a CLR structure, in that way we can use all objects definitions from CatFactory as we want. In this guide, I'll show you how to generate a data dictionary from an existing database.

Background

There are a large number of tools for generating data dictionaries from existing databases but with the preview release for CatFactory, we can generate data dictionary in DotNet Core.

Usually in SQL Server, we can store the description for objects in extended properties, we add a description in MS_Description extended property's value and then we can import those values to get information about database objects.

Using the Code

Step 01 - Create Console Project

Create a new console project with Visual Studio.

Step 02 - Add Packages for Console Project

Add the following package in project.json file:

Step 03 - Add Class for Console Project

Add the following files for console project:

1. Extensions.cs
2. DataDictionaryCodeBuilder.cs

Now modify the code for each file:

Code for Extensions.cs file:

using System;
using System.Collections.Generic;
using System.Reflection;

namespace ConsoleApp4
{
    public static class Extensions
    {
        public static String GetAttributes(this Object attributes)
        {
            var items = new List<String>();

            foreach (var property in attributes.GetType().GetProperties())
            {
                items.Add(String.Format("{0}=\"{1}\"", 
                property.Name.Replace("-", "_"), 
                property.GetValue(attributes, null)));
            }

            return String.Join(" ", items);
        }
    }
}

Code for DataDictionaryCodeBuilder.cs file:

using System;
using System.Text;
using CatFactory.CodeFactory;
using CatFactory.Mapping;

namespace ConsoleApp4
{
    public class DataDictionaryCodeBuilder : CodeBuilder
    {
        public DataDictionaryCodeBuilder(Database db)
        {
            Database = db;
        }

        public Database Database { get; }

        public override String FileName
        {
            get
            {
                return String.Format("{0}.DataDictionary", Database.Name);
            }
        }

        public override String FileExtension
        {
            get
            {
                return "html";
            }
        }

        public String OpenTag(String name, Object attributes = null)
        {
            if (attributes == null)
            {
                return String.Format("<{0}>", name);
            }
            else
            {
                return String.Format("<{0} {1}>", name, attributes.GetAttributes());
            }
        }

        public String CloseTag(String name)
        {
            return String.Format("</{0}>", name);
        }

        public String GetTag(String name, Object content, Object attributes = null)
        {
            if (attributes == null)
            {
                return String.Format("<{0}>{1}</{0}>", name, content);
            }
            else
            {
                return String.Format("<{0} {1}>{2}</{0}>", 
                name, attributes.GetAttributes(), content);
            }
        }

        public String GetTag(String name)
        {
            return String.Format("<{0} />", name);
        }

        public override String Code
        {
            get
            {
                var output = new StringBuilder();

                output.AppendFormat("{0}", OpenTag("html"));

                output.AppendFormat("{0}", OpenTag("head"));

                output.AppendFormat("{0}", OpenTag("style"));

                output.AppendFormat
                ("body {{ font-family: Verdana; font-size: 12px; }}");

                output.AppendFormat
                (".table 
                {{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");

                output.AppendFormat(".view 
                {{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");

                output.AppendFormat(".storedProcedure 
                {{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");

                output.AppendFormat(".columns 
                {{ border: 1px solid black; font-family: Verdana; font-size: 12px; }}");

                output.AppendFormat(".parameters 
                {{ border: 1px solid black; font-family: Verdana; font-size: 12px; }}");

                output.AppendFormat(".alternateRow {{ background-color: #C0C0C0; }}");

                output.AppendFormat("{0}", CloseTag("style"));

                output.AppendFormat("{0}", CloseTag("head"));

                output.AppendFormat("{0}", OpenTag("body"));

                output.AppendFormat("{0}", GetTag("h1", 
                $"Data Dictionary for {Database.Name}", 
                new { id = "top", style = "text-align: center;" }));

                output.AppendFormat("{0}", GetTag("h2", "Tables"));
                output.AppendFormat("{0}", OpenTag("ol"));

                foreach (var table in Database.Tables)
                {
                    output.AppendFormat("{0}", GetTag("li", 
                    GetTag("a", table.FullName, new { href = $"#{table.FullName}" })));
                }

                output.AppendFormat("{0}", CloseTag("ol"));

                output.AppendFormat("{0}", GetTag("h2", "Views"));
                output.AppendFormat("{0}", OpenTag("ol"));

                foreach (var view in Database.Views)
                {
                    output.AppendFormat("{0}", GetTag("li", 
                    GetTag("a", view.FullName, new { href = $"#{view.FullName}" })));
                }

                output.AppendFormat("{0}", CloseTag("ol"));

                output.AppendFormat("{0}", GetTag("h2", "Stored Procedures"));
                output.AppendFormat("{0}", OpenTag("ol"));

                foreach (var procedure in Database.Procedures)
                {
                    output.AppendFormat("{0}", GetTag("li", 
                    GetTag("a", procedure.FullName, new { href = $"#{procedure.FullName}" })));
                }

                output.AppendFormat("{0}", CloseTag("ol"));

                foreach (var table in Database.Tables)
                {
                    output.AppendFormat("{0}", OpenTag("table", 
                    new { id = table.FullName, @class = "table" }));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Type"));
                    output.AppendFormat("{0}", GetTag("td", 
                    String.Format("User Table {0}", GetTag("a", 
                    "Go top", new { href = "#top" }))));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Schema"));
                    output.AppendFormat("{0}", GetTag("td", 
                    GetTag("strong", table.Schema)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Name"));
                    output.AppendFormat("{0}", GetTag("td", 
                    GetTag("strong", table.Name)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Description"));
                    output.AppendFormat("{0}", GetTag("td", 
                    GetTag("strong", table.Description)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Columns"));
                    output.AppendFormat("{0}", GetTag("td", table.Columns.Count));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("br"));

                    output.AppendFormat("{0}", 
                    OpenTag("table", new { @class = "columns" }));

                    output.AppendFormat("{0}", 
                    GetTag("caption", "Columns list"));

                    output.AppendFormat("{0}", OpenTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("th", "Name"));
                    output.AppendFormat("{0}", GetTag("th", "Type"));
                    output.AppendFormat("{0}", GetTag("th", "Length"));
                    output.AppendFormat("{0}", GetTag("th", "Prec"));
                    output.AppendFormat("{0}", GetTag("th", "Scale"));
                    output.AppendFormat("{0}", GetTag("th", "Nullable"));
                    output.AppendFormat("{0}", GetTag("th", "Collation"));
                    output.AppendFormat("{0}", GetTag("th", "Description"));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    for (var i = 0; i < table.Columns.Count; i++)
                    {
                        var column = table.Columns[i];

                        if (i % 2 == 0)
                        {
                            output.AppendFormat("{0}", OpenTag("tr"));
                        }
                        else
                        {
                            output.AppendFormat("{0}", 
                            OpenTag("tr", new { @class = "alternateRow" }));
                        }

                        output.AppendFormat("{0}", GetTag("td", column.Name));
                        output.AppendFormat("{0}", GetTag("td", column.Type));
                        output.AppendFormat("{0}", GetTag("td", column.Length));
                        output.AppendFormat("{0}", GetTag("td", column.Prec));
                        output.AppendFormat("{0}", GetTag("td", column.Scale));
                        output.AppendFormat("{0}", 
                        GetTag("td", column.Nullable ? "Yes" : "No"));
                        output.AppendFormat("{0}", GetTag("td", column.Collation));
                        output.AppendFormat("{0}", GetTag("td", column.Description));
                        output.AppendFormat("{0}", CloseTag("tr"));
                    }

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("hr"));
                    output.AppendFormat("{0}", GetTag("br"));
                }

                foreach (var view in Database.Views)
                {
                    output.AppendFormat("{0}", OpenTag("table", 
                    new { id = view.FullName, @class = "view" }));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Type"));
                    output.AppendFormat("{0}", GetTag("td", 
                    String.Format("View {0}", GetTag("a", 
                    "Go top", new { href = "#top" }))));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Schema"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", view.Schema)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Name"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", view.Name)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Description"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", view.Description)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Columns"));
                    output.AppendFormat("{0}", GetTag("td", view.Columns.Count));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("br"));

                    output.AppendFormat("{0}", 
                    OpenTag("table", new { @class = "columns" }));

                    output.AppendFormat("{0}", 
                    GetTag("caption", "Columns list"));

                    output.AppendFormat("{0}", OpenTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("th", "Name"));
                    output.AppendFormat("{0}", GetTag("th", "Type"));
                    output.AppendFormat("{0}", GetTag("th", "Length"));
                    output.AppendFormat("{0}", GetTag("th", "Prec"));
                    output.AppendFormat("{0}", GetTag("th", "Scale"));
                    output.AppendFormat("{0}", GetTag("th", "Nullable"));
                    output.AppendFormat("{0}", GetTag("th", "Collation"));
                    output.AppendFormat("{0}", GetTag("th", "Description"));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    for (var i = 0; i < view.Columns.Count; i++)
                    {
                        var column = view.Columns[i];

                        if (i % 2 == 0)
                        {
                            output.AppendFormat("{0}", OpenTag("tr"));
                        }
                        else
                        {
                            output.AppendFormat("{0}", 
                            OpenTag("tr", new { @class = "alternateRow" }));
                        }

                        output.AppendFormat("{0}", GetTag("td", column.Name));
                        output.AppendFormat("{0}", GetTag("td", column.Type));
                        output.AppendFormat("{0}", GetTag("td", column.Length));
                        output.AppendFormat("{0}", GetTag("td", column.Prec));
                        output.AppendFormat("{0}", GetTag("td", column.Scale));
                        output.AppendFormat("{0}", 
                        GetTag("td", column.Nullable ? "Yes" : "No"));
                        output.AppendFormat("{0}", GetTag("td", column.Collation));
                        output.AppendFormat("{0}", GetTag("td", column.Description));
                        output.AppendFormat("{0}", CloseTag("tr"));
                    }

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("hr"));
                    output.AppendFormat("{0}", GetTag("br"));
                }

                foreach (var storedProcedure in Database.Procedures)
                {
                    output.AppendFormat("{0}", OpenTag("table", 
                    new { id = storedProcedure.FullName, @class = "storedProcedure" }));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Type"));
                    output.AppendFormat("{0}", GetTag("td", 
                    String.Format("Stored Procedure {0}", 
                    GetTag("a", "Go top", new { href = "#top" }))));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Schema"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", storedProcedure.Schema)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Name"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", storedProcedure.Name)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Description"));
                    output.AppendFormat("{0}", 
                    GetTag("td", GetTag("strong", storedProcedure.Description)));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("td", "Parameters"));
                    output.AppendFormat("{0}", 
                    GetTag("td", storedProcedure.Parameters.Count));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("br"));

                    output.AppendFormat("{0}", 
                    OpenTag("table", new { @class = "parameters" }));

                    output.AppendFormat("{0}", 
                    GetTag("caption", "Parameters list"));

                    output.AppendFormat("{0}", OpenTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tr"));
                    output.AppendFormat("{0}", GetTag("th", "Name"));
                    output.AppendFormat("{0}", GetTag("th", "Type"));
                    output.AppendFormat("{0}", GetTag("th", "Length"));
                    output.AppendFormat("{0}", GetTag("th", "Prec"));
                    output.AppendFormat("{0}", GetTag("th", "Scale"));
                    output.AppendFormat("{0}", GetTag("th", "Collation"));
                    output.AppendFormat("{0}", GetTag("th", "Order"));
                    output.AppendFormat("{0}", CloseTag("tr"));

                    output.AppendFormat("{0}", CloseTag("thead"));

                    output.AppendFormat("{0}", OpenTag("tbody"));

                    for (var i = 0; i < storedProcedure.Parameters.Count; i++)
                    {
                        var parameter = storedProcedure.Parameters[i];

                        if (i % 2 == 0)
                        {
                            output.AppendFormat("{0}", OpenTag("tr"));
                        }
                        else
                        {
                            output.AppendFormat("{0}", 
                            OpenTag("tr", new { @class = "alternateRow" }));
                        }

                        output.AppendFormat("{0}", GetTag("td", parameter.Name));
                        output.AppendFormat("{0}", GetTag("td", parameter.Type));
                        output.AppendFormat("{0}", GetTag("td", parameter.Length));
                        output.AppendFormat("{0}", GetTag("td", parameter.Prec));
                        output.AppendFormat("{0}", GetTag("td", parameter.Scale));
                        output.AppendFormat("{0}", GetTag("td", parameter.Collation));
                        output.AppendFormat("{0}", GetTag("td", parameter.ParamOrder));
                        output.AppendFormat("{0}", CloseTag("tr"));
                    }

                    output.AppendFormat("{0}", CloseTag("tbody"));
                    output.AppendFormat("{0}", CloseTag("table"));

                    output.AppendFormat("{0}", GetTag("hr"));
                    output.AppendFormat("{0}", GetTag("br"));
                }

                output.AppendFormat("{0}", CloseTag("body"));

                output.AppendFormat("{0}", CloseTag("html"));

                return output.ToString();
            }
        }
    }
}

using System;
using CatFactory.SqlServer;

namespace ConsoleApp1
{
    public class Program
    {
        public static void Main(String[] args)
        {
            var dbFactory = new SqlServerDatabaseFactory
            {
                ConnectionString = 
                "server=(local);database=AdventureWorks2012;integrated security=yes;"
            };

            var db = dbFactory.Import();

            var dataDictionary = new DataDictionaryCodeBuilder(db)
            {
                OutputDirectory = "C:\\Temp"
            };

            dataDictionary.CreateFile();
        }
    }
}

For this example, I'm targeting an existing database on my local SQL Server instance and a local directory. Obviously, you can change those values but before, please make sure you have the rights to read/write database and directory, please make sure about that to avoid common execution errors.

Save all changes and run your console program and check on your output directory the generated file or check the sample file in download list.

How it works? We import the definition for existing database with CatFactory and then we read all tables and views. Please make sure the flag ImportMSDescription has "true" value and in this case, build a couple of HTML tables to represent the information.

I know the style for HTML output file. It's a crap but this guide is not about how to get a pretty HTML, it's about how to generate a data dictionary from an existing database. If you want to apply a pretty style to the output file, you can modify the code for DataDictionaryCodeBuilder class.

Output:

Points of Interest

• Unfortunately, there isn't compatibility with DocX for DotNet Core, I know it's better to generate a Word document instead of simple HTML file but for now we generate an HTML file.

Code Improvements

• Add functions
• Add custom data types

Related Links

1. Generating Code for EF Core with CatFactory

History

• 31^st December, 2016: Initial version