[Request] sp_doc - virtual PK, real and virtual Relations (FK, object references, column references)

[aisbergde]

Is your feature request related to a problem? Please describe.

Most PK and relations in my databases are virtual, most of them between views. I also want to document them. There are 3 kinds of relations I want to document

• virtual FK (together with real FK)
• references between objects (data lineage)
• references between table and view columns (data lineage on column level)

Describe the solution you'd like

There should be a way to write them into the database and to read them out for documentation. A good idea would be to use extended properties to write this information into the database and to use them. There could be a specific syntax (specific names for these properties and a specific format) which could be recognized by sp_doc

Currently, I am working on an open source project to connect an additional repository database to a normal database, I sync some metadata with extended properties, there are also virtual PK, virtual index, virtual relations, SQL code parsing to detect column data lineage and some more: https://github.com/DataHandwerk/DataHandwerk-toolkit-mssql It is not yet documented, only some description of the architecture, for example https://raw.githack.com/DataHandwerk/DataHandwerk-toolkit-mssql/main/docs/build/html5/dhw-arc42.html#section-building-block-view

The question is: how to document this, how to visualize.

Describe alternatives you've considered

• https://elsasoft.com/ sqlspec. I used this great software for many years, some virtual dependencies could be added using XML comments. But the further development was finished a few years ago.
• http://schemaspy.org/ has some way to add virtual relations
• dbeaver has virtual PK and virtual relations and can visualize them together with real PK / FK. One idea is to create the json file where dbeaver stores the virtual objects to visualize them in dbeaver interactively. But there is no support for 3 different kinds of relations (FK, object references, column references). And this is nothing what could be called a documentation for a database user.
• https://www.dbml.org/home/ (DBML - Database Markup Language) is one more idea: to export database descriptions in this language and to try to visualize, for example using https://dbdiagram.io/home?utm_source=dbml or https://github.com/softwaretechnik-berlin/dbml-renderer.
  • But there are not many ways to use these DBML scripts for documentation. There is an alpha or beta version at https://dbdocs.io/
  • There is no support for 3 different kinds of relations (FK, object references, column references), but I could generate different dbml scripts
• I tested to use PowerBI and its graph object visualization, using tables or columns as nodes and relations as edges. I am not really satisfied with the result.
• I found this interesting project, written in GO, to document databases and relations. It could be extended to document virtual relations and views, but the development has stopped. I would need to learn GO to extend this: https://github.com/timabell/schema-explorer

[lowlydba]

Interesting project and ideas! This would be a pretty big scope and I'd like to make the approach as generic as possible if it were to be added to sp_doc.

If I'm understanding correctly, a basic implementation may look something like:

• Using a specific EP property (i.e. FK_xxxx) on a view's columns --> display the same as a table's real FK
  • Not sure about fetching the underlying table's FKs since there is a lot of potential for transformations of columns, thus making the value returned in the view very different from the source table's FK value, which could be confusing or misleading. This could also already be referenced elsewhere in the doc with a little extra scrolling. Maybe providing a table with links to the source table(s) of views would be a good middle ground?
• Using a specific EP property (i.e. PK_xxxx) on a view's columns --> display the same as a table's real PK
• Option to show all non-description EPs in a separate table
  • This would be a simple approach to allow you to show any number of metadata properties

I'm going to be busy with some personal things for the next month or two, so not sure how much time I can dedicate to this short term, but I think its a really promising enhancement and will noodle over the idea. Of course, feel free to open a pull request as well in the mean time :)

[aisbergde]

I could learn how this procedure is built and try to create pull requests step by step

• define the best format to store PK (it should be possible to define composed FK)
• read additional PK from EP and use it in sp_doc
• define a format how to store FK in EP (it needs to contain the target table and possible composed keys)
• read and use this EP

[lowlydba]

I should finally have some time to work on this soon.

I am planning to start with the generic "show all EPs in a table" approach and then do some testing to see if I can efficiently auto-detect when an EP value refers to a column or other object, then make it a link.

This would be the most simple approach to implement and to use, since it wouldn't require defining custom key/value formats at all.

[lowlydba]

@aisbergde The new parameter @AllExtendedProperties is now on the dev branch, if you want to do any light QA since you seem to have great edge cases in your existing data :)

It should allow you to view n extended properties for any given column/object/etc. which I hope will be a big step forward for your needs.

[aisbergde]

Currently I am on holidays. I could do this only in 2 weeks. But you should not wait.