{"id":85774,"date":"2019-12-03T18:41:04","date_gmt":"2019-12-03T18:41:04","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85774"},"modified":"2020-01-09T17:14:41","modified_gmt":"2020-01-09T17:14:41","slug":"the-gloop-an-easier-way-of-managing-sql-server-documentation","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-gloop-an-easier-way-of-managing-sql-server-documentation\/","title":{"rendered":"Managing SQL Server Documentation as JSON: Per-Object Extracts, MongoDB Tooling, and Collective Editing"},"content":{"rendered":"

Here, in this blog, I\u2019m continuing a theme that I started in a previous blog, \u2018What\u2019s in that database? Getting information about routines<\/a>\u2019.<\/p>\n

In that blog, I just wanted to provide a few examples of extracting metadata from SQL Server into Powershell and hinting about why one might want to do it. I\u2019ll now show how to save the details of the metadata of your database, including tables and routines, in JSON files. Then I\u2019ll demonstrate how to change and add to the descriptions of database objects and saving them to the database.<\/p>\n

Metadata extract files are handy for documentation, study, cataloguing and change-tracking. This type of file supplements source because it can record configuration, permissions, dependencies and documentation much more clearly. It is a good way of making a start with documenting your database.<\/p>\n

Here is a sample of a json metadata file (from AdventureWorks 2016). It was generated using GloopCollectionOfObjects.sql that is here in Github<\/a>, and is being viewed in JSONBuddy. I use this format of JSON, a collection of documents representing SQL Server base objects (no parent objects) when I need to read the contents into MongoDB. The term \u2018Gloop\u2019 refers to a large query that, you\u2019d have thought, would be better off as a procedure. Here is a typical sample of the output.<\/p>\n

<\/p>\n

Ok. Those descriptions make a huge difference to readability. I want to be able to edit them in JSON and save them back to the database. OK. We can do that. I use #ParseJSONMetadataToUpdateTheDocumentation<\/a> which is in the GitHub folder. It is a temporary stored procedure. We can either use this from a SQL batch or call it in SQL using PowerShell.<\/p>\n

Without wishing to change our AdventureWorks file, We\u2019ll open up a sample file called \u2018Customers\u2019 and add some descriptions. In this next screendump, I\u2019m adding a description to a table called Customer.note<\/p>\n

<\/p>\n

Then we save the file and execute this code.<\/p>\n

USE customers\r\nDECLARE @JSON NVARCHAR(MAX);\r\nSELECT @JSON = BulkColumn\r\n  FROM\r\n  OpenRowset(BULK 'PathToTheData\\customers.json', SINGLE_NCLOB)\r\n  AS MyJSONFile;\r\nDECLARE @howManyChanges INT;\r\nEXECUTE #ParseJSONMetadataToUpdateTheDocumentation @JSON, @howManyChanges OUTPUT;\r\nSELECT Convert(VARCHAR(5),@howManyChanges)+ ' descriptions were either changed or added'\r\n<\/pre>\n
 <\/p>\n
<\/p>\n
Why 2? I added a description to a column as well.<\/p>\n
\u2026 and we can check to make sure that the right thing got changed.<\/p>\n
<\/p>\n
If we delete all the extended properties and then re-run the batch we get this<\/p>\n
<\/p>\n
Phew, they are all back in there.<\/p>\n
What\u2019s the best way to execute and write out the SQL Gloop query to get the metadata extract in the first place? It can be done in SQL, but once you\u2019ve decided that you need to work on a whole collection of servers and their databases, or you have a regular database chore to do that involves saving to files, it is worth considering doing the chore in SQL, but using a PowerShell task<\/a> that can be scheduled. I’ve included the code for this on Github\u00a0called RunSQLScript.ps1<\/a><\/p>\n
In my previous Blog, I introduced the idea of getting metadata about your database using SQL, and saving the contents into a directory, but I didn\u2019t really go into the details about how you run such code. I also mentioned that, if you are running the same task on a number of databases, there is no shame attached to running a large query to extract the metadata from your databases. I referred to this as a \u2018Gloop\u2019. The reason for doing so is that you can run it on a number of databases but leave no trace of utility procedures on them, not even a temporary stored procedure. GloopCollectionOfObjects.sql<\/a> is one of these and I\u2019ve added others to the Github site that were written for other purposes.<\/p>\n
Developing other uses for Metadata extracts<\/h1>\n
There are several good uses for metadata extracts of database objects and their associated columns, indexes, parameters and return values. The main reason I have for wanting to do this is to see what\u2019s been properly documented in the database and where something is missing, adding it. There are plenty of other uses. What you collect depends on the nature and purpose of your task. I\u2019ve added some sample Gloop queries to deliver different formats of JSON files. Beware, though, that you can\u2019t save the documentation without altering the SQL code in the temporary procedure that shreds it into a relational table.<\/p>\n
Whatever use that one puts these metadata extracts to, one thing always happens: When you are looking at metadata in another format than the build script, then some things, mostly mistakes and omissions, that you have just never noticed before will now stick out clearly. It seems to help to get a different view of your database.<\/p>\n
We must also decide on the format we want for our data. If we are predominately using it for documentation, then readability is important. For doing comparisons or keeping a record of database changes in source control, maybe you want something that can be read by input routines more easily. Although ordered arrays are legal JSON, they aren\u2019t easy to produce in SQL Server because SQL Server\u2019s JSON library is geared to produce key\/value pairs. Tables have names that translate easily to keys since the schema\/name combination is unique. The same is true of columns and parameters. The natural way of recording these might be to have an array of schemas, each of which have arrays of table objects using their name as a key. However, they are ordered arrays of objects. It isn\u2019t a major difficulty because we can do what SQL Server does, and put the name as a value assigned to a \u2018name\u2019 key. It just looks clunky if you are reading the JSON. However, it makes it easier to query in MongoDB, and to read into relational table format.<\/p>\n
Editing JSON-based metadata With Studio3T<\/h2>\n
With Studio3T, you can just import each file as a collection into a database. I use a MongoDB database Called SQLServerMetadata<\/strong>, and import each database as a collection under its SQL Server name. This provides me with a separate collection for each database, with a document representing a base object such as a Function, Table, Procedure or View. This allows me to edit each object individually and save it back to the collection. Then I can export it back out.<\/p>\n
<\/p>\n
Here, I\u2019m just beginning to document the table itself. I can edit the documentation for each table individually because each base object (Function, Table, Procedure, Rule, View or Default) is a document in MongoDB terms and I can, with Studio3T, edit each document separately and have it checked instantly as I save the edits. This means I can even edit the documentation for procedures and functions, their parameters and columns (table-valued functions and views have them- it is helpful to your team members to document them)<\/p>\n
Having made my changes, I then save them back to the file. I do it via Studio3T\u2019s collection-export facilities. I use<\/p>\n
<\/p>\n
\u2026 and on the next page in the wizard \u2026<\/p>\n
<\/p>\n
I can even paste individual documents from Studio3T into SQL and see that I\u2019ve made all the necessary changes. Unfortunately, MongoDB inserts surrogate primary keys in a form of \u2018extended JSON\u2019 that isn\u2019t compatible. You need to nick them out. To do this you can use a Regex. In a file, or in SSMS, use \u2026 
\n\u2018”_id”.+\u2019 
\n\u2026as the regex expression (without the single bracket delimiters!), and a blank replacement text<\/p>\n
You can then inspect the results using the SQL that that is used within the ParseJSONMetadataToUpdateTheDocumentation.sql on Github<\/a>.<\/p>\n
DECLARE @JSON NVARCHAR(MAX);\r\nSELECT @JSON = BulkColumn\r\n  FROM\r\n  OpenRowset(BULK 'PathToTheData\\customers.json', SINGLE_NCLOB)\r\n  AS MyJSONFile;\r\n\r\nDROP TABLE IF EXISTS #TheObjects;\r\nCREATE TABLE #TheObjects\r\n  (\r\n  Name sysname NOT NULL,\r\n  Type NVARCHAR(30) NOT NULL,\r\n  Description NVARCHAR(3750) NULL,\r\n  ParentName sysname NULL,\r\n  [Contains] NVARCHAR(MAX) NULL\r\n  );\r\n\r\nINSERT INTO #TheObjects (Name, Type, Description, ParentName, [Contains])\r\n  SELECT BaseObjects.Name, BaseObjects.Type, BaseObjects.Description, NULL,\r\n    [Contains]\r\n    FROM\r\n    OpenJson(@JSON)\r\n    WITH\r\n      (\r\n      Name NVARCHAR(80) '$.Name', Type NVARCHAR(80) '$.Type',\r\n      Parent NVARCHAR(80) '$.Parent',\r\n      Description NVARCHAR(MAX) '$.Description',\r\n      [Contains] NVARCHAR(MAX) '$.contains' AS JSON\r\n      ) AS BaseObjects;\r\n\r\nINSERT INTO #TheObjects (Name, Type, Description, ParentName, [Contains])\r\n  SELECT objvalues.Name, obj.[Key] AS Type, objvalues.Description,\r\n    #TheObjects.Name AS ParentName, NULL AS [contains]\r\n    FROM #TheObjects\r\n      OUTER APPLY OpenJson(#TheObjects.[Contains]) AS child\r\n      OUTER APPLY OpenJson(child.Value) AS obj\r\n      OUTER APPLY\r\n    OpenJson(obj.Value)\r\n    WITH (Name NVARCHAR(80) '$.Name', Description NVARCHAR(MAX) '$.Description') AS objvalues;\r\n\r\n\r\nDROP TABLE IF EXISTS #EPParentObjects;\r\nCREATE TABLE #EPParentObjects\r\n  (\r\n  TheOneToDo INT IDENTITY(1, 1),\r\n  level0_type VARCHAR(128) NULL,\r\n  level0_Name sysname NULL,\r\n  level1_type VARCHAR(128) NULL,\r\n  level1_Name sysname NULL,\r\n  level2_type VARCHAR(128) NULL,\r\n  level2_Name sysname NULL,\r\n  [Description] NVARCHAR(3750),\r\n  );\r\n\r\nINSERT INTO #EPParentObjects\r\n  (level0_type, level0_Name, level1_type, level1_Name, level2_type,\r\nlevel2_Name, Description)\r\n  SELECT 'schema' AS level0_type, ParseName(Name, 2) AS level0_Name,\r\n      CASE WHEN Type LIKE '%FUNCTION%' THEN 'FUNCTION'\r\n        WHEN Type LIKE '%TABLE%' THEN 'TABLE'\r\n        WHEN Type LIKE '%PROCEDURE%' THEN 'PROCEDURE'\r\n        WHEN Type LIKE '%RULE%' THEN 'RULE'\r\n        WHEN Type LIKE '%VIEW%' THEN 'VIEW'\r\n        WHEN Type LIKE '%DEFAULT%' THEN 'DEFAULT'\r\n        WHEN Type LIKE '%AGGREGATE%' THEN 'AGGREGATE'\r\n        WHEN Type LIKE '%LOGICAL FILE NAME%' THEN 'LOGICAL FILE NAME'\r\n        WHEN Type LIKE '%QUEUE%' THEN 'QUEUE'\r\n        WHEN Type LIKE '%RULE%' THEN 'RULE'\r\n        WHEN Type LIKE '%SYNONYM%' THEN 'SYNONYM'\r\n        WHEN Type LIKE '%TYPE%' THEN 'TYPE'\r\n        WHEN Type LIKE '%XML SCHEMA COLLECTION%' THEN 'XML SCHEMA COLLECTION' \r\n\t    ELSE'UNKNOWN' \r\n\t  END AS level1_type,\r\n    ParseName(Name, 1) AS level1_Name, NULL AS level2_type,\r\n    NULL AS level2_Name, Description\r\n    FROM #TheObjects\r\n    WHERE ParentName IS NULL;\r\n\r\nINSERT INTO #EPParentObjects\r\n  (level0_type, level0_Name, level1_type, level1_Name, level2_type,\r\nlevel2_Name, Description)\r\n  SELECT level0_type, level0_Name, level1_type, level1_Name,\r\n      CASE WHEN Type LIKE '%COLUMN%' THEN 'COLUMN'\r\n        WHEN Type LIKE '%CONSTRAINT%' THEN 'CONSTRAINT'\r\n        WHEN Type LIKE '%EVENT NOTIFICATION%' THEN 'EVENT NOTIFICATION'\r\n        WHEN Type LIKE '%INDEX%' THEN 'INDEX'\r\n        WHEN Type LIKE '%PARAMETER%' THEN 'PARAMETER'\r\n        WHEN Type LIKE '%TRIGGER%' THEN 'TRIGGER' \r\n\t\tELSE 'UNKNOWN' \r\n\t  END AS Level2_type,\r\n    #TheObjects.Name AS Level2_name, #TheObjects.Description\r\n    FROM #EPParentObjects\r\n      INNER JOIN #TheObjects\r\n        ON level1_Name = ParseName(ParentName, 1) \r\n\t\t  AND level0_Name =ParseName(ParentName, 2);\r\n\r\nSELECT * FROM #EPParentObjects AS EPO<\/pre>\n
 <\/p>\n
If you think that this table looks suspiciously like the parameters you\u2019d need to use for the various SQL system procedures that you\u2019d need to use, you\u2019re right. That is the underlying purpose of the code!<\/p>\n
\n<\/p>\n
Hmm. Looks good.<\/p>\n
Studio3T has the option of putting the resulting JSON for a database on the clipboard so you can even do the job without having to think of a way of reading the JSON into SQL Server. However, I’ve already demonstrated an easy way of doing that.<\/p>\n
So with\u00a0ParseJSONMetadataToUpdateTheDocumentation.sql,<\/a> along with the Gloop, you have the means to get a report of your database in JSON, edit it to add or change the documentation and then read it back into the database to update it.\u00a0<\/p>\n
Conclusions<\/h1>\n
This is an approach to documentation that suits me fine. I can see a great deal about each table or routine while I check through the documentation. I can add information or make changes and save the results back to the development database.\u00a0 If you know of a better way, let me know! There are a number of different ways that you can do this, so all you need to do is to edit the Gloop to be closer to what you need. Make sure that, if you change the structure of the JSON produced by the Gloop, you make the equivalent changes to the SQL that shreds the JSON into a relational format for updating your documentation.<\/p>\n
Just as a thought. If you put the JSON for your documentation in source control, you can then use it for inserting the documentation into a database build. It is a lot easier than the conventional way. It means that, if you keep it up to date, it also allows you to track the changes in the database even if you are using a migrations-scripts-first approach.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
An alternative approach to SQL Server database documentation: extract each object’s metadata (tables, routines, columns, indexes) as individual JSON files, then manage the collection using MongoDB-compatible tools like Studio 3T for bulk editing, querying, and cross-object analysis. Includes the PowerShell extraction scripts and a discussion of when this approach beats extended-properties-in-database documentation.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[],"coauthors":[6813],"class_list":["post-85774","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85774","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\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85774"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85774\/revisions"}],"predecessor-version":[{"id":86095,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85774\/revisions\/86095"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85774"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85774"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}