Click here to Skip to main content
14,336,321 members

Generating Data Dictionary for SQL Server with CatFactory

Rate this:
2.83 (4 votes)
Please Sign up or sign in to vote.
2.83 (4 votes)
8 Jan 2017CPOL
HowTo for generating a data dictionary from existing database

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:

NameVersionDescription
CatFactory.SqlServer1.0.0-alpha-build11Provides import database feature for SQL Server

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:

Data dictionary output

Data dictionary 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

  • 31st December, 2016: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

HHerzl
Software Developer
El Salvador El Salvador
CatFactory Creator.

Full Stack Developer with Experience in C#, Entity Framework Core, ASP.NET Core and Angular.

Comments and Discussions

 
Question[My vote of 1] Code Dump! Pin
Vander Wunderbar8-Jan-17 9:25
memberVander Wunderbar8-Jan-17 9:25 
You just dump a code. Nothing else.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 31 Dec 2016

Stats

8.5K views
114 downloads
5 bookmarked