{"id":85547,"date":"2019-10-29T16:09:53","date_gmt":"2019-10-29T16:09:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85547"},"modified":"2019-10-29T16:09:53","modified_gmt":"2019-10-29T16:09:53","slug":"associating-data-directly-with-sql-server-database-objects","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/associating-data-directly-with-sql-server-database-objects\/","title":{"rendered":"Associating Data Directly with SQL Server Database Objects."},"content":{"rendered":"

It is easy to attach details and documentation to a SQL Server database using extended properties. In fact, you can add a number of items of information to any database objects such as tables, views, procedures or users. If you use JSON to store the information, then you can, in addition, even monitor trends by storing previous information as a back history of changes. This could include such information as the date of changes, or variables such as the size of the database or table at a particular date, with the object.<\/p>\n

I\u2019ll use as an example the applying of version numbers to a database. We\u2019ll end up storing old version numbers and the date when they were applied. We\u2019d want to do this to build up a history of when changes were made to a database. This allows us to find out various items of information as well as the current version: We can, for example, find out when, and how long, a database was at a version number.<\/p>\n

Storing a version number for a database in JSON.<\/h1>\n

Let\u2019s take things in easy steps.<\/p>\n

Without a history<\/h2>\n

Imagine that you have a database called \u2018AdventureWorks\u2019 that you need to document. You might have several different facts that you need to store: a description maybe, more likely a version number. There are likely to be other facts you need to document. You might decide to store it a JSON so that you can access just part of the data.<\/p>\n

DECLARE @DatabaseInfo NVARCHAR(3750)\r\nSELECT @DatabaseInfo =\r\n  (\r\n  SELECT 'AdventureWorks' AS \"Name\", '2.45.7' AS \"Version\",\r\n  'The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles.' AS \"Description\",\r\n    GetDate() AS \"Modified\",\r\n\tSUser_Name() AS \"by\"\r\n  FOR JSON PATH\r\n  );\r\n\r\nIF not EXISTS\r\n  (SELECT name, value  FROM fn_listextendedproperty(\r\n     N'Database_Info',default, default, default, default, default, default) )\r\n    EXEC sys.sp_addextendedproperty @name=N'Database_Info', @value=@DatabaseInfo\r\nELSE\r\n  EXEC sys.sp_Updateextendedproperty  @name=N'Database_Info', @value=@DatabaseInfo\r\n<\/pre>\n
You can now view this in SSMS, of course<\/p>\n
<\/p>\n
You can access it in SQL via various different techniques depending on your preferences.<\/p>\n
You can just access the current version information, or any other value, using JSON_VALUE()<\/p>\n
SELECT Json_Value((SELECT Convert(NVARCHAR(3760), value)\r\n  FROM sys.extended_properties AS EP\r\n  WHERE major_id = 0 AND minor_id = 0 \r\n    AND name = 'Database_Info'),'$.Version') AS Version\r\n<\/pre>\n
You can get the data as a result in various forms, including a single row or one row per key. Let\u2019s first get the JSON value from the extended property \u2026<\/p>\n
DECLARE @DatabaseInfo NVARCHAR(3750);\r\nSELECT @DatabaseInfo = Convert(NVARCHAR(3760), value)\r\n  FROM sys.extended_properties AS EP\r\n  WHERE major_id = 0 AND minor_id = 0 AND name = 'Database_Info';\r\n<\/pre>\n
.. then you can get the data as a result with a single row \u2026<\/p>\n
SELECT *\r\n  FROM\r\n  OpenJson(@DatabaseInfo)\r\n  WITH (Name sysname, Version NVARCHAR(30), Description NVARCHAR(3000), Modified DATETIME2,\r\n[by] NVARCHAR(30)\r\n);\r\n<\/pre>\n
\u2026or you can get the result with one row per key.<\/p>\n
SELECT TheProperties.[Key], TheProperties.Value\r\n  FROM OpenJson(@DatabaseInfo) AS TheJson\r\n    OUTER APPLY OpenJson(TheJson.Value) AS TheProperties;\r\n<\/pre>\n
<\/p>\n
This data isn\u2019t entirely secure. You need CONTROL or ALTER\u00a0permissions on the object to alter it, but it can be accessed by anyone who has VIEW DEFINITION permission<\/a>. We can demonstrate this now by creating a \u2018headless\u2019 user without a login and assigning just that permission. You can try this out with various permissions to see what works!<\/p>\n
USE AdventureWorks2016;\r\n-- create a user\r\nCREATE USER EricBloodaxe WITHOUT LOGIN;\r\nGRANT VIEW DEFINITION ON DATABASE::\"AdventureWorks2016\" TO EricBloodaxe;\r\nEXECUTE AS USER = 'EricBloodaxe';\r\nPRINT CURRENT_USER;\r\nSELECT Convert(NVARCHAR(3760), value)\r\n  FROM sys.extended_properties AS EP\r\n  WHERE major_id = 0 AND minor_id = 0 AND name = 'Database_Info';\r\nREVERT;\r\nDROP USER EricBloodaxe;\r\nPRINT CURRENT_USER;\r\n<\/pre>\n
Storing a history as well<\/h2>\n
At this point, you probably decide that you really want more than this. What you really need is to be able to keep track of versions and when they happened, something like this \u2026.<\/p>\n
{ \r\n   \"Name\":\"MyCDCollection\",\r\n   \"Version\":\"3.4.05\",\r\n   \"Description\":\"Every book on databases for developers used to include one as an example\",\r\n   \"Modified\":\"2019-10-21T11:44:53.810\",\r\n   \"by\":\"EricBloodaxe\",\r\n   \"History\":[ \r\n      { \r\n         \"Modified\":\"2019-10-21T11:44:03.703\",\r\n         \"by\":\"dbo\",\r\n         \"Version\":\"3.4.00\"\r\n      },\r\n      { \r\n         \"Modified\":\"2019-10-21T11:44:13.717\",\r\n         \"by\":\"GenghisKahn\",\r\n         \"Version\":\"3.4.01\"\r\n      },\r\n      { \r\n         \"Modified\":\"2019-10-21T11:44:23.733\",\r\n         \"by\":\"AtillaTheHun\",\r\n         \"Version\":\"3.4.02\"\r\n      },\r\n      { \r\n         \"Modified\":\"2019-10-21T11:44:33.763\",\r\n         \"by\":\"VladTheImpaler\",\r\n         \"Version\":\"3.4.03\"\r\n      },\r\n      { \r\n         \"Modified\":\"2019-10-21T11:44:43.790\",\r\n         \"by\":\"KaiserBull\",\r\n         \"Version\":\"3.4.04\"\r\n      }\r\n   ]\r\n}\r\n<\/pre>\n
(Taken from one of the test-harnesses. You\u2019ll notice that we are doing very rapid CI!). Here, we have a database that we are continually updating but we have kept a record of our old versions, when they happened and who did the alterations.<\/p>\n
We can access the history like this<\/p>\n
SELECT * FROM OpenJson(\r\n  (SELECT Json_Query((SELECT Convert(NVARCHAR(3760), value)\r\n    FROM sys.extended_properties AS EP\r\n    WHERE major_id = 0 AND minor_id = 0 \r\n      AND name = 'Database_Info'),'strict $.History')))\r\n  WITH (WhenModified DATETIME2 '$.Modified',\r\n\t    WhoDidIt sysname '$.by',\r\n\t\t[Version] NVARCHAR(30) '$.Version' )\r\n<\/pre>\n
<\/p>\n
..but it would be better to add in the current version like this<\/p>\n
DECLARE @Info nvarchar(3760)=(SELECT Convert(NVARCHAR(3760), value)\r\n    FROM sys.extended_properties AS EP\r\n    WHERE major_id = 0 AND minor_id = 0 \r\n      AND name = 'Database_Info')\r\nSELECT * FROM OpenJson(\r\n  (SELECT Json_Query(@Info,'strict $.History')))\r\n  WITH (WhenModified DATETIME2 '$.Modified',\r\n\t    WhoDidIt sysname '$.by',\r\n\t\t[Version] NVARCHAR(30) '$.Version' )\r\nUNION ALL \r\nSELECT Json_Value(@Info,'strict $.Modified'),\r\n       Json_Value(@Info,'strict $.by'),\r\n\t   Json_Value(@Info,'strict $.Version')\r\n<\/pre>\n
<\/p>\n
We maintain the current record where it is easy to get to and simply add an array to hold the history information. Our only headache is that we can only hold an NVARCHAR of 3750 characters (7500 of varchar characters) because extended properties are held as SQL_Variants. They need careful handling<\/a>! This means that if our JSON data is larger, we have to trim off array elements that make the JSON exceed that number.<\/p>\n
There is an error in the JSON_MODIFY() function that means that it doesn\u2019t actually delete an array element, but merely assigns it to NULL. This can only be rectified by removing the NULL because one generally removes the oldest members of an array that you just append to by deleting element[0]. If it is NULL it still exists. Doh!<\/p>\n
Once we have this up and running, there is a way of storing all sorts of ring-buffer information for reporting purposes that is in sorted order. Yes, you\u2019re right, you now have a way of estimating database or table growth and performing a host of other monitoring tasks.<\/p>\n
Because the code is rather more complicated, we\u2019ll use a stored procedure. I\u2019m making this a temporary procedure because I like to keep \u2018utility\u2019 code away from database code.<\/p>\n
CREATE OR ALTER  PROCEDURE #ApplyVersionNumberToDatabase\r\n@Version NVARCHAR(30) =  '2.45.7', \r\n@Name sysname = 'AdventureWorks', --only needed the first time around\r\n@Description NVARCHAR(3000) =     --only needed the first time around\r\n  'The AdventureWorks Database supports a fictitious, multinational \r\n  manufacturing company called Adventure Works Cycles.'\r\nas\r\n\r\nDECLARE \r\n  @CurrentContents NVARCHAR(4000);\r\n--get the current values if any\r\nSELECT @CurrentContents = Convert(NVARCHAR(3750), value)\r\n  FROM fn_listextendedproperty(\r\nN'Database_Info', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT\r\n);\r\n--if there is nothing there yet ...\r\nIF @CurrentContents IS NULL\r\n  BEGIN --just simply write it in\r\n    DECLARE @DatabaseInfo NVARCHAR(3750);\r\n    SELECT @DatabaseInfo =\r\n      N'{\"Name\":\"' + String_Escape(@Name, 'json') + N'\",\"Version\":\"'\r\n      + String_Escape(@Version, 'json') + N'\",\"Description\":\"'\r\n      + String_Escape(@Description, 'json') + +N'\",\"Modified\":\"'\r\n      + Convert(NVARCHAR(28), GetDate(), 126) + N'\",\"by\":\"'\r\n      + String_Escape(current_user, 'json') + N'\",\"History\":[]}'; -- empty history array\r\n    EXEC sys.sp_addextendedproperty @name = N'Database_Info',\r\n@value = @DatabaseInfo;\r\n  END;\r\nELSE\r\n  BEGIN --place the current values in the history array\r\n-- SQL Prompt formatting off\r\n\tSELECT @CurrentContents=\r\n\t  Json_Modify(@CurrentContents, 'append $.History',Json_query(\r\n\t   '{\"Modified\":\"'\r\n\t    +Json_Value(@CurrentContents,'strict $.Modified')\r\n\t  +'\",\"by\":\"'\r\n\t    +Json_Value(@CurrentContents,'strict $.by')\r\n\t  +'\",\"Version\":\"'\r\n\t    +Json_Value(@CurrentContents,'strict $.Version')\r\n\t+'\"}'))\r\n\t--now just overwrite the current values\r\n\tSELECT @CurrentContents=Json_Modify(@CurrentContents, 'strict $.Version',@version)\r\n\tSELECT @CurrentContents=Json_Modify(@CurrentContents, 'strict $.Modified',\r\n\t                                      Convert(NVARCHAR(28), GetDate(), 126))\r\n\tSELECT @CurrentContents=Json_Modify(@CurrentContents, 'strict $.by',current_user)\r\n-- SQL Prompt formatting on\r\n    --if the json won't fit the space (unlikely) then take out the oldest records\r\n    DECLARE @bug INT = 10; --limit every loop to a sane value just in case...\r\n    WHILE Len(@CurrentContents) > 3750 AND @bug > 0\r\n      BEGIN\r\n        SELECT @CurrentContents =\r\n          Json_Modify(@CurrentContents, 'strict $.History[1]', NULL),\r\n          @bug = @bug - 1;\r\n\t\t  --SQL Server JSON can't delete array elements, it just replaces them\r\n\t\t  --with a null, so we have to remove them manually. \r\n\t\t  SELECT @CurrentContents=Replace(@CurrentContents,'null,' COLLATE DATABASE_DEFAULT,'') \r\n      END;\r\n    EXEC sys.sp_updateextendedproperty @name = N'Database_Info',\r\n@value = @CurrentContents;\r\n    PRINT 'updated';\r\n  END;\r\n<\/pre>\n
The way that this works is that you only need to put in the name of the database and the description first time around, or after you\u2019ve deleted it.<\/p>\n
Here is how you delete it.<\/p>\n
EXEC sys.sp_dropextendedproperty @name = N'Database_Info';<\/pre>\n
The following code be necessary the first time around, especially if you\u2019ve used different defaults for your temporary stored procedure.<\/p>\n
EXECUTE #ApplyVersionNumberToDatabase @Version='3.4.00',@Name='MyCDCollection',\r\n@Description='Every book on databases for developers used to include one as an example'<\/pre>\n
From then on, it is just a matter of providing the version number<\/p>\n
EXECUTE #ApplyVersionNumberToDatabase @Version='3.5.02'<\/pre>\n
Testing it out<\/h1>\n
Here is one of the test routines that I used for the stored procedure, but without the checks on the version number, as that would be repetition.<\/p>\n
EXEC sys.sp_dropextendedproperty @name = N'Database_Info';\r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.00'\r\nWAITFOR DELAY '00:00:10'\r\nCREATE USER GenghisKahn WITHOUT LOGIN\r\nGRANT alter ON database::\"AdventureWorks2016\" TO GenghisKahn \r\nEXECUTE AS USER = 'GenghisKahn' \r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.01'\r\nREVERT\r\nDROP USER GenghisKahn\r\nWAITFOR DELAY '00:00:10'\r\n-- create a user\r\nCREATE USER AtillaTheHun WITHOUT LOGIN\r\nGRANT alter ON database::\"AdventureWorks2016\" TO AtillaTheHun \r\nEXECUTE AS USER = 'AtillaTheHun' \r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.02'\r\nREVERT\r\nDROP USER AtillaTheHun\r\nWAITFOR DELAY '00:00:10'\r\n-- create a user\r\nCREATE USER VladTheImpaler WITHOUT LOGIN\r\nGRANT alter ON database::\"AdventureWorks2016\" TO VladTheImpaler \r\nEXECUTE AS USER = 'VladTheImpaler' \r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.03'\r\nREVERT\r\nDROP USER VladTheImpaler\r\nWAITFOR DELAY '00:00:10'\r\n-- create a user\r\nCREATE USER KaiserBull WITHOUT LOGIN\r\nGRANT alter ON database::\"AdventureWorks2016\" TO KaiserBull \r\nEXECUTE AS USER = 'KaiserBull' \r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.04'\r\nREVERT\r\nDROP USER KaiserBull\r\nWAITFOR DELAY '00:00:10'\r\n-- create a user\r\nCREATE USER EricBloodaxe WITHOUT LOGIN\r\nGRANT alter ON database::\"AdventureWorks2016\" TO EricBloodaxe \r\nEXECUTE AS USER = 'EricBloodaxe' \r\nEXECUTE #ApplyVersionNumberToDatabase @Version='3.4.05'\r\nREVERT\r\nDROP USER EricBloodaxe\r\n<\/pre>\n
Conclusion<\/h1>\n
Extended properties are useful for development work but wonderful for reporting on a database or monitoring a trend. If you use JSON to store the data, they can act as miniature tables, or more correctly ring buffers. They have a variety of uses and are easy to create and remove without making any changes to the database whatsoever. This is because Extended properties and their values are either ignored as changes by humans and deployment\/comparison tools, or you can easily configure them to be ignored: They do not affect the version)<\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
It is easy to attach details and documentation to a SQL Server database using extended properties. In fact, you can add a number of items of information to any database objects such as tables, views, procedures or users. If you use JSON to store the information, then you can, in addition, even monitor trends by……<\/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],"tags":[],"coauthors":[6813],"class_list":["post-85547","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85547","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=85547"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85547\/revisions"}],"predecessor-version":[{"id":85554,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85547\/revisions\/85554"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85547"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}