Why do you have to go to the live database to see what objects are in it? It is all in the source code, even if it isn\u2019t always easy to find. It is likely to be nicely commented too. This goes particularly for tables and their columns, constraints and indexes. You may not even have a live database of the version you\u2019re interested in checking out; you\u2019ve just got the source code. What do you do then?<\/p>\n\n\n\n
Even if you have access to the live server, information about tables isn\u2019t that intuitive to get. Unlike routines, such as procedures and functions, the actual source of a SQL Server table, with its comments and descriptions, isn\u2019t stored in SQL Server. I can\u2019t think of a good reason for this, beyond that fact that a table represents a complex assemblage of objects and properties rather than a single object.<\/p>\n\n\n\n
I\u2019ll be describing a way of getting this information. Although one can do much more, the code in this article can be used merely for the useful task of cataloguing the source code. A PowerShell one-liner, specifying the source code of a file or directory, can produce reports like this using sample database source code as input.<\/p>\n\n\n\n <\/p>\n\n\n\n <\/p>\n\n\n\n You might argue that all you need is the name of the columns and their datatypes, and these are nicely displayed in SSMS. Nope. One of the handiest ways of understanding a routine is to look at its source in the metadata, especially if it has comments and documentation in it. Just a few well-chosen words can save plenty of time for the developers and DBAs maintaining a system.<\/p>\n\n\n\n It is a great advantage to have this documentation in the live database. It is irritating to have to go to and fro between the source code and the live database just to see what a column or constraint is for, or to understand why a table was designed the way it is. It is much easier to have the information with the actual table in a way that can be queried in SQL. With a large database, it is so much easier to find a particular column if you can search not only by name or by datatype but by comment!<\/p>\n\n\n\n If you are so inclined, you can, using the techniques I describe here, store every table script as an extended property attached to the table, but I\u2019ll be describing how you can, using PowerShell, even tease out all the end-of-line comments and block comments from the source and store them as extended properties with their associated column, table, constraint or index.<\/p>\n\n\n\n With all this information in extended properties, you can get lists of tables or columns complete with explanations. I occasionally use code that reverse-engineers a build script<\/a> complete with the reconstituted comments. Having the information in place makes security checks easier too, such as \u2018Are all tables containing personal data encrypted?\u2019.<\/p>\n\n\n\n It has always been a problem that documentation in the source, where it should be, is not then passed into the live database when the build script is executed. In a table, you have columns, constraints and indexes that you are likely to document using line-ending comments and block comments. You probably have a big block comment at the start, explaining the table. This information should be available in the live database. Microsoft don\u2019t have a good answer and vaguely go on about adding comments in extended properties. Well, that\u2019s fine but it hasn\u2019t happened, unsurprisingly: Have you ever tried to do it? It is an almost impossible task, even with SQL Doc<\/a>.<\/p>\n\n\n\n My solution is to execute my finely-documented build script as usual to create the latest version of the database, and then process the same script in PowerShell to add all the comments and documentation as extended properties in the right place in the live database.<\/p>\n\n\n\n I use a Microsoft SQL Server parser for .NET to get the documentation, and the script then checks the live database. If the documentation for the table, column, constraint or index isn\u2019t in the database then it puts it there. Any build script generated from the live database will have the script for the documentation included there as extended properties.<\/p>\n\n\n\n Here in this article I\u2019ll introduce a PowerShell function that does the hard work. Before we get too far into the details, here it is parsing a table. A string containing a table (it could be a database of tables) is passed to the function. It produces an object that describes the table. We convert it to JSON merely to make it easy to display.<\/p>\n\n\n\n If we look at the jSON, we can see what it makes of the Create script<\/p>\n\n\n\n But you\u2019d want to process an entire table build script to find out what\u2019s in it. Here is the script I used to get the list of tables I displayed earlier (you need to put the correct path in the filespec), and you can get the file I used from GitHub<\/a>.<\/p>\n\n\n\n And, of course I ought to include the one that listed the columns. The content variable is re-used from the previous snippet.<\/p>\n\n\n\n Here is the PowerShell script. It uses a State Machine, because the script is working on the streamed output from a parser. The meaning of any token extracted from the code in sequence depends on what preceded it. Occasionally, it has more than one possible meaning dependent on what follows it. All this can be managed naturally in a state machine, even though the code looks unsettlingly different. Never mind, because you just execute it rather than look at it. (If you have trouble importing the sqlserver module, check here<\/a> for more information.)<\/p>\n\n\n\n The current version of this code is contained in the GitHub project here<\/a><\/p>\n\n\n\n A state machine will read a series of inputs. When it reads an input that belongs to a particular set of values associated with that state, it will switch to a different state. Each state specifies which state to switch to, for a given input. This means that you have a script where complexity can be limited to a single place and the script is very easy to amend. It is curious that, as you better-understand and develop the algorithm, one can pull code out, and the code shrinks.<\/p>\n\n\n\n The parsing routine ends up with an object that represents a set of tables. Basically, you just want the name and type of the property or object so you can use the information. I suspect there are other uses for it, but I couldn\u2019t resist getting datatypes for columns.<\/p>\n\n\n\n If the code finds something that isn\u2019t a table script, it skids through it naturally, because it never finds that \u2018table\u2019 token. Hopefully, it recovers to the correct state to proceed on to the next statement to see if it is a Table script. (Hopefully because I haven\u2019t yet tested it with every possible CREATE statement).<\/p>\n\n\n\n The output of the function is a list of table objects, or maybe just one. Each object consists of its name, the type, and any comment blocks or end-of-line comments.<\/p>\n\n\n\n Each object has three lists. The columns, the constraints and the inline indexes. These all contain the name, the type and the comment. It is a PowerShell object but you can easily convert this to JSON, YAML or XML for export. With a bit of tweaking you can convert it to CSV, save it to disk and put it in Excel or whatever you wish.<\/p>\n\n\n\n It is very easy to pass an object like the one produced by this PowerShell Script to a procedure. This procedure can be used to update the extended properties of the table, column, constraint or index. I use temporary procedures because I don\u2019t like adding utility procedures in databases. We start with the code for the procedure. Sadly, it uses JSON so it will only work with versions of SQL Server that have this implemented.<\/p>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/04\/word-image-47.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/04\/word-image-48.png\")
<\/figure>\n\n\n\nWhy Get the Documentation into the Database?<\/h2>\n\n\n\n
Documentation in the Wrong Place<\/h2>\n\n\n\n
Using a PowerShell Parse-TableDDLScript Function<\/h2>\n\n\n\n
$object=Parse-TableDDLScript @\"\nCREATE TABLE dbo.PurchaseOrderDetail \/* this provides the details of the PurchaseOrder *\/\n(\n PurchaseOrderID int NOT NULL--the purchase order ID--Primary key.\n REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),-- Foreign key to PurchaseOrderHeader.PurchaseOrderID.\n LineNumber smallint NOT NULL,--the line number\n ProductID int NULL --Product identification number. Foreign key to Product.ProductID.\n REFERENCES Production.Product(ProductID), --another foreign key\n UnitPrice money NULL, --Vendor's selling price of a single product.\n OrderQty smallint NULL,--Quantity ordered\n ReceivedQty float NULL,--Quantity actually received from the vendor.\n RejectedQty float NULL, -- Quantity rejected during inspection.\n DueDate datetime NULL, --Date the product is expected to be received.\n rowguid uniqueidentifier ROWGUIDCOL NOT NULL --the rowguid\n CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (NEWID()),--the named constraibt\n ModifiedDate datetime NOT NULL --Date and time the record was last updated.\n CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (GETDATE()),--Default constraint value of GETDATE()\n LineTotal AS ((UnitPrice*OrderQty)), --Per product subtotal. Computed as OrderQty * UnitPrice.\n StockedQty AS ((ReceivedQty-RejectedQty)), --Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.\n CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber --the primary key\n PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber) --combining PurchaseOrderID and LineNumber\n WITH (IGNORE_DUP_KEY = OFF) --with options\n)\nON [PRIMARY];\ngo\n\"@\n$object|ConvertTo-Json -Depth 5<\/pre>\n\n\n\n
{\n \"TableName\": \"dbo.PurchaseOrderDetail\",\n \"Documentation\": \" this provides the details of the Individual products and is used together\\r\\nwith General purchase order information associated with dbo.PurchaseOrderHeaderthat provides specific purchase order. \",\n \"Columns\": [\n {\n \"Name\": \"PurchaseOrderID\",\n \"Type\": \"int NOT NULL\",\n \"Documentation\": \"the purchase order ID--Primary key.\"\n },\n {\n \"Name\": \"LineNumber\",\n \"Type\": \"smallint NOT NULL\",\n \"Documentation\": \"the line number\"\n },\n {\n \"Name\": \"ProductID\",\n \"Type\": \"int NULL\",\n \"Documentation\": \"Product identification number. Foreign key to Product.ProductID.\"\n },\n {\n \"Name\": \"UnitPrice\",\n \"Type\": \"money NULL\",\n \"Documentation\": \"Vendor's selling price of a single product.\"\n },\n {\n \"Name\": \"OrderQty\",\n \"Type\": \"smallint NULL\",\n \"Documentation\": \"Quantity ordered\"\n },\n {\n \"Name\": \"ReceivedQty\",\n \"Type\": \"float NULL\",\n \"Documentation\": \"Quantity actually received from the vendor.\"\n },\n {\n \"Name\": \"RejectedQty\",\n \"Type\": \"float NULL\",\n \"Documentation\": \" Quantity rejected during inspection.\"\n },\n {\n \"Name\": \"DueDate\",\n \"Type\": \"datetime NULL\",\n \"Documentation\": \"Date the product is expected to be received.\"\n },\n {\n \"Name\": \"rowguid\",\n \"Type\": \"uniqueidentifierRowguidCol NOT NULL\",\n \"Documentation\": \"the rowguid\"\n },\n {\n \"Name\": \"ModifiedDate\",\n \"Type\": \"datetime NOT NULL\",\n \"Documentation\": \"Date and time the record was last updated.\"\n },\n {\n \"Name\": \"LineTotal\",\n \"Type\": \" computed\",\n \"Documentation\": \"Per product subtotal. Computed as OrderQty * UnitPrice.\"\n },\n {\n \"Name\": \"StockedQty\",\n \"Type\": \" computed\",\n \"Documentation\": \"Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.\"\n }\n ],\n \"Constraints\": [\n {\n \"Name\": \"*FK1\",\n \"Type\": \"Foreign key (anonymous) for PurchaseOrderID\",\n \"Documentation\": \" Foreign key to PurchaseOrderHeader.PurchaseOrderID.\"\n },\n {\n \"Name\": \"*FK2\",\n \"Type\": \"Foreign key (anonymous) for ProductID\",\n \"Documentation\": \"another foreign key\"\n },\n {\n \"Name\": \"*D3\",\n \"Type\": \"Default (anonymous) for DueDateDefault \",\n \"Documentation\": \"default duedate to today's date\"\n },\n {\n \"Name\": \"DF_PurchaseOrderDetail_rowguid\",\n \"Type\": \"Default \",\n \"Documentation\": \"the named constraibt\"\n },\n {\n \"Name\": \"DF_PurchaseOrderDetail_ModifiedDate\",\n \"Type\": \"Default \",\n \"Documentation\": \"Default constraint value of GETDATE()\"\n }\n ],\n \"Indexes\": []\n}<\/pre>\n\n\n\n$content = [System.IO.File]::ReadAllText('<path to>\\Customers Database Documented.sql')\n(Parse-TableDDLScript $content).GetEnumerator()|\n Select @{label=\"Table\";expression={$_.TableName}},\n @{label=\"Description\";expression={$_.Documentation}}|\n Format-Table<\/pre>\n\n\n\n(Parse-TableDDLScript $content).GetEnumerator()|\n Select @{label=\"column\";expression={$_.columns}},\n @{label=\"Table\";expression={$_.TableName}} -PipelineVariable table|\n Foreach{$_.column} |# foreach {$table}|convertTo-json\n Select @{label=\"Table\";expression={$table.Table}},\n @{label=\"Column\";expression={$_.Name}},\n @{label=\"DataType\";expression={$_.Type}},\n @{label=\"Description\";expression={$_.Documentation}}|Format-Table<\/pre>\n\n\n\nThe Parse-TableDDLScript<\/h2>\n\n\n\n
import-module sqlserver -Global\n \n function Parse-TableDDLScript\n{\n param\n (\n $Sql # the string containing one or more SQL Server tables\n )\n<#\nThis uses the Microsoft parser to iterate through the tokens and construct enough details of \nthe table to get the various components such as columns, constraints and inline indexes, along\nwith the comments and documentation\n#>\n $msms = 'Microsoft.SqlServer.Management.SqlParser'\n $Assembly = [System.Reflection.Assembly]::LoadWithPartialName(\"$msms\")\n $psr = \"$msms.Parser\"\n $ParseOptions = New-Object \"$psr.ParseOptions\"\n $ParseOptions.BatchSeparator = 'GO'\n $anonymous=1 #the number of anonymous constraints\n $FirstScript = [Microsoft.SqlServer.Management.SqlParser.Parser.Parser]::Parse(\n $SQL, $ParseOptions)\n $State = 'StartOfExpression' #this is the current state\n $TableDetails = @{ TableName = $null; Documentation = $null; \n Columns = @(); Constraints = @(); Indexes = @() }\n $AllTablesDetails = @() #the returned list of tableDetails found in the script\n $ListItemType = 'unknown' # we start y not knowing what sort of object is in the create staement\n $LineCommentRegex = [regex] '(?im)--(.*)\\r' # to clean up an end-of-line comment\n $MultiLineCommentRegex = [regex] '(?ism)\/\\*(.*)\\*\/' # to clean up a block comment\n $ListItemDetails = @{ } #the details of the current column found if any\n $ExpressionLevel = 0 #used to work out whether you are in an expression\n $FirstScript.script.tokens | select Text, Id, Type | foreach {\n # if this works out, we've found the first create statement\n if ($_.Type -eq ';') { $State = 'StartOfExpression' }\n Write-Verbose \"State:'$state', Type:'$($_.Type)', Expressionlevel:'$ExpressionLevel' ListItemType:'$ListItemType'\"\n if ($_.Type -eq 'TOKEN_CREATE')\n {\n #If the state is 'initialBlock, it is expecting a create statement \n if ($state -eq 'initialBlock') \n { $blockComment = $TableDetails.Documentation }; #because it is cleared out\n $state = 'CreateStatement' # change state\n }\n # now we need to keep tabs of the expression level based on the nesting of the brackets\n if ($_.Type -eq '(') { $ExpressionLevel++ } #to deal with bracketed expressions- start of expression\n if ($_.Type -eq ')')\n {\n $ExpressionLevel--;\n if ($ExpressionLevel -eq 0)\n {\n # end of the table script so save anything and initialise.\n # deal with the problem of having several CREATE statements in a batch\n if (($state -ne 'CreateStatement') -or ($_.Type -eq 'LEX_BATCH_SEPERATOR')) \n { $State = 'StartOfExpression'; }\n # here we reset the state, expression level and ListItem type\n $ExpressionLevel = 0;\n $ListItemType = 'unknown';\n \n if ($TableDetails.TableName -ne $null) #then there is something there\n {\n Write-Verbose \"storing table $($TableDetails.TableName)\";\n $AllTablesDetails += $TableDetails;\n }\n \n write-verbose \"$state zapping table details\"\n $TableDetails = @{ TableName = $null; Documentation = $null; \n Columns = @(); Indexes = @(); Constraints = @() }\n $blockcomment = $null;\n } #save any existing table \n \n } #to deal with bracketed expressions- end of expression\n #is it the start of a list?\n if (($_.Type -eq '(') -and ($ExpressionLevel -eq 1)) { $ListItemType = 'unknown' }\n # if we definitely have a CREATE TABLE so...\n if ($state -notin ('CreateStatement', 'StartOfExpression')) \n {\n # Do we need to save any current object and then reinitialise?\n # to keep this operation in one place we have some complicated condition tests here\n if (($_.Type -eq ',' -and $ExpressionLevel -eq 1 #if it is the start of a new list item\n ) -or ($_.Type -in @('LEX_BATCH_SEPERATOR',\n 'TOKEN_INDEX',\n 'TOKEN_CONSTRAINT') #change in object being defined\n ) -or ($_.Type -eq ')' -and $expressionLevel -eq 0\n # or it is one of the anonymous constraints such as a default constraint or a foreign key constraint\n ) -or (($ListItemType -ne 'Constraint') -and\n ($_.Type -in @('TOKEN_FOREIGN','TOKEN_DEFAULT','TOKEN_REFERENCES'))))\n { #we've found a new component of the table\n # we have to make sure that we've got the current line saved\n $State = 'startOfLine'; #OK. This is the start of a list item of the create statement\n #we save the details of the previous list item\n if ($ListItemDetails.Name -ne $null) # if it exists, save it\n {\n if ($ListItemType -eq 'column')\n {\n # if we are needing to save the column whose details we collected ...\n $TableDetails.columns += $ListItemDetails;\n Write-Verbose \"column found $($ListItemDetails.Name) $($ListItemDetails.Documentation)\";\n }\n if ($ListItemType -eq 'constraint')\n {\n # if we are needing to save the constraint whose details we assembled ...\n $TableDetails.constraints += $ListItemDetails;\n Write-Verbose \"constraint found and added to $($TableDetails.constraints | convertto-json)\";\n }\n if ($ListItemType -eq 'index')\n {\n # if we are needing to save the index whose details we gathered ...\n $TableDetails.Indexes += $ListItemDetails;\n Write-Verbose \"Index found $($ListItemDetails.Name) $($ListItemDetails.Documentation)\";\n } # so now we try to work out what sort if list item we have\n if ($_.Type -in @('TOKEN_CONSTRAINT', 'TOKEN_FOREIGN'))\n { $ListItemType = 'constraint' }\n elseif ($_.Type -eq 'TOKEN_INDEX') { $ListItemType = 'index' }\n elseif ($_.Type -eq 'TOKEN_REFERENCES')\n {\n $ListItemType = 'constraint'\n $State = 'Identifier';\n $ListItemDetails = \n @{ Name = '*FK'+($anonymous++); \n Documentation = $null ; \n Type = 'Foreign key (anonymous) for '+$ListItemdetails.Name };\n }\n elseif ($_.Type -eq 'TOKEN_DEFAULT')\n {\n $ListItemType = 'constraint'\n $State = 'Identifier';\n $ListItemDetails = \n @{ Name = '*D'+($anonymous++); \n Documentation = $null; \n Type = 'Default (anonymous) for '+$ListItemdetails.Name };\n } else { $ListItemType = 'unknown' }\n }\n }\n } #end of list item (column or table constraint or index)\n if ($State -eq 'CreateStatement')\n #we are looking for the first token which will be a table name. \n #If no table name, the expression must be ignored.\n {\n if ($_.Type -eq 'TOKEN_TABLE')\n { $state = 'WhatNameIsTable'; $ListItemType = 'table' }\n #the table can be in several different consecutive tokens following this\n };\n if ($State -eq 'identifier') # it could be adding the NOT NULL constraint\n {if ($_.Type -eq 'TOKEN_NOT') {$ListItemDetails.Type+=' NOT'}\n if ($_.Type -eq 'TOKEN_NULL') {$ListItemDetails.Type+=' NULL'}\n }\n # we may want to remember the actual data type of a column. This token follows a column name \n if (($State -eq 'GetDataType') -and ($_.Type -eq 'TOKEN_ID') -and $ExpressionLevel -eq 1)\n { $ListItemDetails.Type = $_.Text; $State = 'identifier' }\n #we need to react according to the type of list item\/line being written\n if (($State -eq 'startOfLine')) \n {\n if (($_.Type -eq 'TOKEN_WITH') -and ($ExpressionLevel -eq 0)) #\n { $State = 'with' } # a TableOption expression is coming\n if (($_.Type -eq 'TOKEN_ON') -and ($ExpressionLevel -eq 0)) #\n { $State = 'on' } # a TableOption expression is coming\n if ($_.Type -eq 'TOKEN_ID')\n {\n if ($ListItemType -eq 'unknown') { $ListItemType = 'column' };\n $ListItemDetails = @{ Name = $_.Text; Documentation = $null; Type = '' };\n $State = 'GetDataType';\n };\n };\n #now save the tokens that tell us about the type of the object\n if ($_.Type -eq 'TOKEN_CLUSTERED') { $ListItemDetails.type += 'Clustered ' }\n if ($_.Type -eq 'TOKEN_NONCLUSTERED') { $ListItemDetails.type += 'NonClustered ' }\n if ($_.Type -eq 'TOKEN_UNIQUE') { $ListItemDetails.type += 'Unique ' } # Token_on\n if ($_.Type -eq 'TOKEN_PRIMARY') { $ListItemDetails.type += 'Primary ' } # Token_on\n if ($_.Type -eq 'TOKEN_ROWGUIDCOL') { $ListItemDetails.type += 'RowguidCol ' }\n if ($_.Type -eq 'TOKEN_DEFAULT') { $ListItemDetails.type += 'Default ' }\n if ($_.Type -eq 'TOKEN_AS') { $ListItemDetails.type += ' computed' }\n if ($_.Type -eq 'TOKEN_s_CTW_DATA_COMPRESSION') { $tableDetails.type += 'Data-compression ' }\n # if we his a batch separator (eg Go) then we save the current table if there is one\n if ($_.Type -in @('LEX_BATCH_SEPERATOR'))\n {\n #gone to expressionLevel\n $State = 'StartOfExpression'\n };\n # store any comments with the current object\n if ($State -in @('startOfLine', 'Identifier', 'GetDataType'))\n {\n if ($_.Type -eq 'LEX_END_OF_LINE_COMMENT')\n {\n # one has to strip out the delimiters in both cases\n $ListItemDetails.Documentation += $LineCommentRegex.Replace($_.Text, '${1}');\n }\n if ($_.Type -eq 'LEX_MULTILINE_COMMENT')\n {\n $ListItemDetails.Documentation += $MultiLineCommentRegex.Replace($_.Text, '${1}');\n }\n }\n if ($State -eq 'WhatNameIsTable') # we are trying to find out the name of the table\n {\n if ($_.Type -in @('TOKEN_ID', '.'))\n {\n $TableDetails.TableName += $_.Text;\n Write-Verbose \"Table name found $($TableDetails.TableName)\";\n }\n if ($_.Type -eq '(' -and $ExpressionLevel -eq 1) { $State = 'startOfLine' };\n };\n if ($_.Type -eq 'LEX_MULTILINE_COMMENT')\n # deal with multiline comments that can be associated with a table\n {\n $blockComment = $MultiLineCommentRegex.Replace($_.Text, '${1}')\n if (($ListItemType -in ('table')) -or \n ($state -in @('StartOfExpression', 'initialBlock')))\n { $TableDetails.Documentation += $blockComment; $blockComment = $null }\n Write-Verbose \"Found block comment $($_.Text) \"\n if ($state -eq 'StartOfExpression') { $state = 'InitialBlock' }\n \n }\n \n }\n \n # now we add the table to the list of tables\n \n if ($TableDetails.TableName -ne $null)\n {\n $AllTablesDetails += $TableDetails;\n $TableDetails = @{ TableName = $null; Documentation = $null; \n Columns = @(); indexes = @(); constraints = @() }\n \n }\n Write-Verbose \"found $($AllTablesDetails.count) tables\"\n $AllTablesDetails\n}\n<\/pre>\n\n\n\nMerging the Documentation to a Database.<\/h2>\n\n\n\n
CREATE OR alter PROCEDURE #AddDocumentation \n@JSON NVARCHAR(MAX), @changed INT output\n\/**\nSummary: >\n This procedure takes a JSON document created by \n the PowerShell SQL Table Parser and either updates\n or creates the documentation in Extended properties\n in the database.\n It checks that all the columns and constraints\n are there as specified in the JSON file to ensure\n that you are sending documentation to the right\n version of the database\nAuthor: Phil Factor\nDate: 03\/04\/2020\nExamples:\n - DECLARE @numberChanged int\n EXECUTE #AddDocumentation @json, @numberChanged OUTPUT\n SELECT @NumberChanged\nReturns: >\n The number of updates or insertions of documention done\n**\/\nAS\nBEGIN\n\/* this table contains the parameters required for the Extended properties\n stored procedures, and some columns used merely for expanding the JSON *\/\nCREATE TABLE #EPParentObjects\n (\n TheOneToDo INT IDENTITY(1, 1),\n level0_type VARCHAR(128) NULL,\n level0_Name sysname NULL,\n level1_type VARCHAR(128) NULL,\n level1_Name sysname NULL,\n level2_type VARCHAR(128) NULL,\n level2_Name sysname NULL,\n [Description] NVARCHAR(3750) NULL,\n [columns] NVARCHAR(MAX) NULL,\n [indexes] NVARCHAR(MAX) NULL,\n constraints NVARCHAR(MAX) NULL\n );\n-- insert the tables into the #EPParentObjects table with their details\n--the details are saved as JSON documents, lists of columns, indexes or constraints.\n INSERT INTO #EPParentObjects\n (level0_type, level0_Name, level1_type, level1_Name, level2_type,\n level2_Name, [Description], [columns], [indexes], constraints)\nSELECT 'schema' AS level0_type, Coalesce(ParseName(Name, 2),'DBO') AS level0_Name,\n 'Table' AS level1_type , ParseName(Name, 1) AS level1_Name, \n\t NULL AS Level2_type,NULL AS Level2_name,\n\t [Description],[columns],[indexes],constraints\n\t FROM OpenJson(@JSON)\n WITH\n (\n Name SYSNAME '$.TableName', \n Description NVARCHAR(3876) '$.Documentation',\n [columns] NVARCHAR(MAX) '$.Columns' AS JSON,\n [indexes] NVARCHAR(MAX) '$.Indexes' AS JSON,\n [constraints] NVARCHAR(MAX) '$.Constraints' AS JSON\n ) AS BaseObjects;\n-- Now we simply cross-apply the contents of the table with the OpenJSON function\n-- for every list (columns,indexes and constraints). By using a UNION, we can do\n-- it all in one statement\nINSERT INTO #EPParentObjects\n (level0_type, level0_Name, level1_type, level1_Name, level2_type,\n level2_Name, Description)\nSELECT level0_type, level0_Name, level1_type, level1_Name, \n\t 'Column' AS level2_type, name AS level2_Name, documentation\nFROM #EPParentObjects \nCROSS APPLY OpenJson([Columns])\n WITH\n (\n Name SYSNAME '$.Name', \n documentation NVARCHAR(3876) '$.Documentation'\n ) WHERE documentation IS NOT null\nUNION ALL\nSELECT level0_type, level0_Name, level1_type, level1_Name,\n\t 'Constraint' AS level2_type, name AS level2_Name, documentation\nFROM #EPParentObjects \nCROSS APPLY OpenJson([Constraints])\n WITH\n (\n Name SYSNAME '$.Name', \n documentation NVARCHAR(3876) '$.Documentation'\n ) WHERE (documentation IS NOT NULL) AND (level2_name NOT LIKE '*#')\nUNION ALL\nSELECT level0_type, level0_Name, level1_type, level1_Name,\n 'Index' AS level2_type, name AS level2_Name, documentation\nFROM #EPParentObjects \nCROSS APPLY OpenJson([Indexes])\n WITH\n (\n Name SYSNAME '$.Name', \n documentation NVARCHAR(3876) '$.Documentation'\n ) WHERE documentation IS NOT null\n\/* the next thing to do is to check that all the objects have corresponding objects in\nthe database. If not, then I raise an error as something is wrong. You could, of course\ndo something milder such as removing failed lines from the result but I wouldn't advise\nit unless you were excpoecting it! *\/\n--first we check the tables\nIF EXISTS (SELECT * FROM #EPParentObjects e\n\tLEFT OUTER JOIN sys.tables\n\tON e.level1_Name=tables.name\n\tAND e.level0_Name=Object_Schema_Name(tables.object_id)\n\tWHERE tables.object_id IS NULL)\n\t RAISERROR('Sorry, but there are one or more tables that aren''t in the DATABASE',16,1)\n--now we check the constrints\nIF EXISTS (SELECT * FROM #EPParentObjects e\n\tLEFT OUTER JOIN sys.objects o\n\tON e.level0_Name=Object_Schema_Name(o.parent_object_id)\n\tAND e.level1_Name=Object_Name(o.parent_object_id)\n\tAND e.level2_Name=o.name AND level2_type ='constraint'\n\tWHERE level2_type ='constraint' AND e.level2_Name NOT LIKE '*%' -- not an anonymous constraint\n\tAND o.object_id IS null\n) RAISERROR('Sorry, but there are one or more constraints that aren''t in the DATABASE',16,1)\n--finally we check the columns.\nIF EXISTS (SELECT * FROM #EPParentObjects e\n\tLEFT OUTER JOIN sys.columns c\n\tON e.level0_Name=Object_Schema_Name(c.object_id)\n\tAND e.level1_Name=Object_Name(c.object_id)\n\tAND e.level2_Name=c.name\n\twhere e.level2_name IS NOT NULL AND level2_type ='Column'\n\tand c.column_id IS null\n) RAISERROR('Sorry, but there are one or more columns that aren''t in the DATABASE',16,1)\n--indexes should be checked in the same way, probably, but these are less frequent.\n--we now iterate through all the lines of the table. Notice that I don't delete \n--documentation if the corresponding JSON record has a null. I just think that\n--it is a bad way of deleting documentation. It is easy to add.\nDECLARE @iiMax int= (SELECT Max(TheOneToDo) FROM #EPParentObjects)\n DECLARE @level0_type VARCHAR(128), @level0_Name sysname,\n @level1_type VARCHAR(128),@level1_Name sysname,\n @level2_type VARCHAR(128),@level2_Name sysname,@Description nvarchar (3750),\n @NeedsChanging BIT,@DidntExist BIT\nDECLARE @ii INT =1\nSELECT @Changed =0\nWHILE @ii<=@iiMax\n BEGIN\n SELECT @level0_type =level0_type, @level0_Name=level0_Name,\n @level1_type =level1_type,@level1_Name =level1_Name,\n @level2_type=level2_type,@level2_Name =level2_Name,@Description=[description]\n FROM #EPParentObjects WHERE TheOneToDo=@ii\n SELECT @NeedsChanging=CASE WHEN value=@description THEN 0 ELSE 1 end --so what is there existing?\n FROM fn_listextendedproperty ('ms_description',\n @level0_type,@level0_Name,@level1_type,\n @level1_Name,@level2_type,@level2_Name) \n IF @@RowCount=0 SELECT @DidntExist=1, @NeedsChanging=CASE WHEN @description IS NULL THEN 0 ELSE 1 END\n IF @NeedsChanging =1\n BEGIN\n SELECT @Changed=@Changed+1\n IF @DidntExist=1\n EXEC sys.sp_addextendedproperty 'ms_description',@description,\n @level0_type,@level0_Name,@level1_type,\n @level1_Name,@level2_type,@level2_Name\n ELSE\n EXEC sys.sp_Updateextendedproperty 'ms_description',@description,\n @level0_type,@level0_Name,@level1_type,\n @level1_Name,@level2_type,@level2_Name \n \n end\n SELECT @ii=@ii+1\n END\nEND<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/04\/word-image-49.png\")
<\/figure>\n\n\n\n