Automatic SQL Server Database Documentation

-
Recently, I've been looking for a good way to automatically generate a data dictionary for multiple databases. Some are internally developed, and others are vendor-provided. My company already had licenses for SAP Power Designer, but the reports generated by that software didn't have a great layout for end-users, and reverse-engineering a PDM from a database would choke on the complicated databases with 100+ tables and other objects. I needed an easy way to use a database or SQL script to generate the documentation.

Criteria:

  • Open-source is preferred, but not mandatory
  • HTML or PDF output
  • List basic objects like tables, indexes, and foreign key references to other tables
  • Show the details on each object, like datatype and included index columns
  • Showing information in extended properties would be nice, but not necessary
  • Editing the metadata would not be necessary, as that wouldn't be allowed on vendor db's
  • Cross-platform support for MS SQL, DB2, and MySQL
  • A wish-list feature would be the ability to include implicit references to other tables, when the columns are used to link two or more tables, but there is not an explicit foreign key relationship. Usually the columns have an identical name, or a naming convention like "ID" in the source table and then "<table>_ID" in the other tables referencing it.

Runner up: Dataedo

This software is not open-source, but it hits all of my criteria, minus the wish-list feature. The only downside is the pricing and the difficulty of getting my work to pay for an enterprise license. I will start the process, but unfortunately I'm not confident it'll get through.

Selection for now: Redgate SQL Doc

Although it's not open source, and doesn't have my wish-list feature, this software does almost everything I need. The UI is nice, installation was easy, and my work already has licenses for Redgate products. The one missing criteria is the cross-platform support, but since MS SQL is ~90% of the databases supported, it'll have to be "good enough" for now.

Comments

Post a Comment

Popular posts from this blog

Adding Font Awesome to .NET MAUI App

-
First, download the desktop version of the Font Awesome fonts. https://fontawesome.com/download Extract the .OTF files to the project's Resources/Fonts folder. You can rename the files to something easier to type/read, if desired.  After copying, check the properties on the files and make sure the Build Action = "MauiFont". Open the MauiProgram.cs file and add the new fonts to the app builder. public static class MauiProgram {   public static MauiApp CreateMauiApp() {     var builder = MauiApp.CreateBuilder();     builder       .UseMauiApp<App>()       .ConfigureFonts(fonts => {         fonts.AddFont("OpenSans-Regular.ttf", "OpenSansRegular");         fonts.AddFont("OpenSans-Semibold.ttf", "OpenSansSemibold");         fonts.AddFont("FontAwesome6Brands.otf", "FontAwesomeBrands");         fonts.AddFont("FontAwesome6Regular.otf", "FontAwesomeR...
Read more

Comparing SELECT Performance of Joining Integers vs GUIDs in SQL Server

-
Image
Overview There are plenty of online conversations and articles asking which makes a better primary key: INT or GUID? There are pros and cons to both, but from my perspective the winner is  INT  by a small margin. As shown in this article , as long as the indexes are maintained to keep fragmentation down the performance is similar, with  INT  slightly ahead in both index size and the speed of CRUD operations. The key is storing the GUID as a UNIQUEIDENTIFIER datatype and never using VARCHAR. Not only is SQL Server less efficient at comparing strings, there's also a chance to run into collation issues comparing across databases. In these discussions, one thing I haven't seen is a comparison of SELECT performance when joining different datatypes. After all, it's rare to find queries that don't stretch across multiple tables in a relational database. I used the StackOverflow2013 database (83 GB size) for testing. Download a copy for yourself from Brent Ozar's  fanta...
Read more

Performance: SQL Server vs MongoDB (JSON data)

-
Anyone reading the title of this post may immediately start to question my sanity and/or competency. After all, SQL is a relational datastore and MongoDB is a document datastore, and wouldn't it just be obvious that a document datastore would be more effective at storing unstructured JSON documents? I have been learning more about NoSQL databases and that's the common wisdom, but I've been around long enough to doubt any hype over any technology. I want to know: how much better? Do the new JSON features in SQL Server 2019 compare well? I wanted to see how both systems perform when the rubber meets the road. Setup To do the comparisons, I'm using DICOM files as the data. The protocol details are too deep to get into here, but they're used in health care to transfer information and images between systems. They are distinct documents, with information stored in tags and sequences, and readily translate to a JSON format . Here's a full example . Updates are not rea...
Read more