dbo.Person<\/strong>. (Script included with the article)<\/p>\n\n\n\n IF Object_Id('dbo.person') IS NOT NULL\n BEGIN\n IF not exists(SELECT * from sys.fn_Listextendedproperty ( N'MS_Description',\n N'SCHEMA', N'dbo',\n N'TABLE', N'Person', \n N'column', N'LastName'))\n EXEC sys.sp_addextendedproperty @name=N'MS_Description', \n @value=N'Persons very last name',\n @level0type = N'SCHEMA', @level0name = N'dbo',\n @level1type = N'TABLE', @level1name = N'Person', \n @level2type = N'column', @level2name = N'LastName'\n ELSE\n EXEC sys.sp_Updateextendedproperty @name = N'MS_Description', \n @value = N'Persons very last name',\n @level0type = N'SCHEMA', @level0name = N'dbo',\n @level1type = N'TABLE', @level1name = N'Person', \n @level2type = N'column', @level2name = N'LastName'\n END<\/pre>\n\n\n\nScripting the Extended Properties<\/h2>\n\n\n\n When you generate a SQL script for a table via SSMS or PowerShell, you get a horrible mess of cluttered code to create each extended property. For an entire database script, this can bulk up the file to over twice its size. To appreciate the mess, just script out AdventureWorks to see what I mean. It takes five hundred words just to script out the Extended Properties of a single table (I chose HumanResources.Employee<\/strong> to check this out.) That script is developed entirely for machines, not us. This type of script soon becomes impossible to use or maintain. It adds a lot of bulk to your table scripts. Adding an extended property via script is next to impossible and finding the extended property you wish to change is a trial. You will strain to determine where documentation is lacking.<\/p>\n\n\n\nHere are three of the Extended Properties, just to show you a flavor<\/p>\n\n\n\n
EXEC sys.sp_addextendedproperty @name = N'MS_Description',\n @value = N'Primary key for Employee records. Foreign key to \n BusinessEntity.BusinessEntityID.',\n @level0type = N'SCHEMA', @level0name = N'HumanResources',\n @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'COLUMN',\n @level2name = N'BusinessEntityID';\n GO\n EXEC sys.sp_addextendedproperty @name = N'MS_Description',\n @value = N'Unique national identification number such as a social security number.',\n @level0type = N'SCHEMA', @level0name = N'HumanResources',\n @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'COLUMN',\n @level2name = N'NationalIDNumber';\n GO\n EXEC sys.sp_addextendedproperty @name = N'MS_Description',\n @value = N'Network login.', @level0type = N'SCHEMA',\n @level0name = N'HumanResources', @level1type = N'TABLE',\n @level1name = N'Employee', @level2type = N'COLUMN', @level2name = N'LoginID';\n GO<\/pre>\n\n\n\nIt also means that documentation is part of the build process, embedded in the DDL for each database object, rather than managed as a separate chore. We can reduce this by a half by using a stored procedure to do the work.<\/p>\n\n\n\n
The HumanResources.Employee<\/strong> table is rather daunting so, to demonstrate this, we create a simple table and then add all the Extended Properties. To try the script out, you must be on SQL Server 2016 or 2017 and will need to install the temporary stored procedure #AddOrUpdateTableDocumentation<\/strong> in the same session. The script for the procedure, and all other code I mention is included with the article in the Downloads section at the base of the article.<\/p>\n\n\n\nIF Object_Id('dbo.person','u') IS NOT NULL DROP TABLE dbo.Person\n GO\n CREATE TABLE Person (\n person_ID INT NOT NULL IDENTITY CONSTRAINT PK_PersonID PRIMARY KEY,\n Title NVARCHAR(8) NULL,\n FirstName VARCHAR(40) NOT NULL,\n MiddleName VARCHAR(40) NULL,\n LastName VARCHAR(40) NOT NULL,\n Suffix NVARCHAR(10) NULL,\n fullName AS ( \n Coalesce(Title+' ','')+Firstname+COALESCE(' '+MiddleName,'')\n \t + ' '+Lastname+ COALESCE(' '+suffix,'')) persisted NOT null,\n ModifiedDate DATETIME NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GetDate()\n );\n CREATE NONCLUSTERED INDEX SearchByPersonLastname\n ON Person(LastName ASC, FirstName ASC)\n WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, \n SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, \n \t ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);\n GO\n EXECUTE #AddOrUpdateTableDocumentation \n '[{\"table\":\"dbo.Person\",\"name\":\"person_ID\",\"type\":\"column\",\n \"doc\":\"surrogate key to guarantee uniqueness \"},\n {\"table\":\"dbo.Person\",\"name\":\"Title\",\"type\":\"column\",\n \"doc\":\"This would be ''Mr'', ''Sir'', or ''Dr'' for example\"},\n {\"table\":\"dbo.Person\",\"name\":\"FirstName\",\"type\":\"column\",\n \"doc\":\"the Firstname or familiar name\"},\n {\"table\":\"dbo.Person\",\"name\":\"MiddleName\",\"type\":\"column\",\n \"doc\":\"optional middle name(s)\"},\n {\"table\":\"dbo.Person\",\"name\":\"LastName\",\"type\":\"column\",\n \"doc\":\"the surname, last name or cultural equivalent\"},\n {\"table\":\"dbo.Person\",\"name\":\"Suffix\",\"type\":\"column\",\n \"doc\":\"optional suffix (e.g. ''II'',''D Phil'',''MD'')\"},\n {\"table\":\"dbo.Person\",\"name\":\"fullName\",\"type\":\"column\",\n \"doc\":\"this is a computed column created from all the parts of the name\"},\n {\"table\":\"dbo.Person\",\"name\":\"ModifiedDate\",\"type\":\"column\",\n \"doc\":\"when modified\"},\n {\"table\":\"dbo.Person\",\"name\":\"DF_ModifiedDate\",\"type\":\"constraint\",\n \"doc\":\"this automatically provides the current date\"},\n {\"table\":\"dbo.Person\",\"name\":\"PK_PersonID\",\"type\":\"constraint\",\n \"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"PK_PersonID\",\"type\":\"Index\",\n \"doc\":\"index created by the PK constraint\"},\n {\"table\":\"dbo.Person\",\"name\":\"SearchByPersonLastname\",\"type\":\"Index\",\n \"doc\":\"the index to allow searches by lastname+ firstname\"}]'<\/pre>\n\n\n\nTo see how SMS would do the same this, just right-click on the table in the browser pane once you\u2019ve run the code that created the table.<\/p>\n\n\n\n<\/p>\n\n\n\n
Yes, it is far more difficult to read. More to the point, it doesn\u2019t actually help you to add the documentation. Also, the script that we use via the #AddOrUpdateTableDocumentation<\/strong> procedure can be used to update the documentation. If you have a newly-built table without any documentation and you want to add the code that attaches the Extended Properties to the table after it is built, then all the values for the Extended Properties would be blank. In our case with our example Person table, it would be\u2026.<\/p>\n\n\n\nEXECUTE #AddOrUpdateTableDocumentation \n '[{\"table\":\"dbo.Person\",\"name\":\"person_ID\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"Title\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"FirstName\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"MiddleName\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"LastName\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"Suffix\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"fullName\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"ModifiedDate\",\"type\":\"column\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"DF_ModifiedDate\",\"type\":\"constraint\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"PK_PersonID\",\"type\":\"constraint\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"PK_PersonID\",\"type\":\"Index\",\"doc\":\"\"},\n {\"table\":\"dbo.Person\",\"name\":\"SearchByPersonLastname\",\"type\":\"Index\",\"doc\":\"\"}]'<\/pre>\n\n\n\nYou\u2019d then want to fill in all those blank values for \u201cdoc”:””<\/strong> as I did in the first listing. It doesn\u2019t take long! If a doc<\/strong> field is left blank, it isn\u2019t touched.<\/p>\n\n\n\nDid I write all that JSON? No of course not. I generated it with a function that builds whatever documentation it finds, in whatever tables you specify, and leaves any undocumented object with a blank value ready for you to add.<\/p>\n\n\n\n
First, before I show you the function to do this, I need a utility function, ListOutTableEPs<\/strong>, that provides all the details of the columns, constraints and indexes for a table. You\u2019ll see that there are certain subtleties: For example, you don\u2019t want to script out documentation for indexes or constraints that are system-named, do you?<\/p>\n\n\n\nCREATE FUNCTION dbo.ListOutTableEPs\n \/**\n Summary: >\n List out all the constraints, indexes, columns and triggers\n of a table in column order \n Author: Phil Factor\n Date: 12\/12\/2017\n Database: PhilFactor\n Examples:\n - Select * from dbo.ListOutTableEPs('dbo.person')\n - >\n SELECT Object_Schema_Name(constraints.object_id) + '.'\n + Object_Name(constraints.object_id) AS [table],\n constraints.name AS name, ChildType AS type, Value AS doc\n FROM dbo.ListOutTableEPs('dbo.person') AS constraints\n ORDER BY column_id, childtype\n FOR JSON AUTO;\n Returns: >\n A table listing all the EPs of type MS_Description if any applied to the children \n of the table name, object_id, column_id, value \n note that the column_id is only relevant for columns and column-based constraints. It \n is mostly used for conveniently ordering the JSON result the same as the build script\n ChildType can be COLUMN, CONSTRAINT, INDEX, (not yet TRIGGER, and NULL).\n **\/\n (\n @Tablename NVarchar(100)\n )\n RETURNS TABLE\n --WITH ENCRYPTION|SCHEMABINDING, ..\n AS\n RETURN\n (\n SELECT ---first we do columns\n col.name, --the name of the database thing\n \t col.object_id,--the object it belongs to\n \t col.column_id,--the column associated with it - used to sort in column order \n Coalesce(Convert(NVARCHAR(4000), ep.value), '') AS value,--the documentation \n \t Convert(VARCHAR(20),'column') AS ChildType -- section is about columns\n FROM sys.columns AS col --the columns\n INNER JOIN sys.objects -- associated with the table \n ON objects.object_id = col.object_id \n \t AND objects.object_id=Object_Id(@tableName,'U') --just the one\n LEFT OUTER JOIN sys.extended_properties AS ep --and pick up any existing documentation\n ON ep.major_id = col.object_id AND ep.class = 1 \n \t AND ep.minor_id = col.column_id\n \t\tAND ep.name='MS_Description' --the microsoft convention\n UNION ALL\n SELECT -- Next we do indexes\n child.name, parent.object_id, 1000, \n \tCoalesce(Convert(NVARCHAR(100), ep.value), ''), 'Index'\n FROM sys.indexes AS child --indexes are treated in a very similar way \n INNER JOIN sys.objects AS parent\n ON child.object_id = parent.object_id\n LEFT OUTER JOIN sys.extended_properties AS ep\n ON ep.major_id = child.object_id \n \t AND ep.minor_id = child.index_id AND ep.class = 7\n \t AND ep.name='MS_Description'--the microsoft convention\n WHERE parent.object_id=Object_Id(@tableName,'U')\n UNION all\n SELECT \n child.name, \n parent.object_id, \n \tCoalesce(DC.parent_column_id, cC.parent_column_id, 1000), \n \tCoalesce(Convert(NVARCHAR(100), ep.value), ''),\n \tCASE WHEN child.type_desc LIKE '%constraint' \n \t THEN 'constraint' \n \t ELSE Lower(Replace(child.type_desc,'SQL_','')) end\n FROM sys.objects AS child\n INNER JOIN sys.objects AS parent\n ON child.parent_object_id = parent.object_id\n \t--we need to gather up information like column and whether\n \t--they are system-generated (who would want to docuement them\n LEFT OUTER JOIN sys.default_constraints AS DC\n ON DC.object_id = child.object_id -- to get column\n LEFT OUTER JOIN sys.check_constraints AS cC\n ON cC.object_id = child.object_id --to get column\n LEFT OUTER JOIN sys.key_constraints AS KC\n ON KC.object_id = child.object_id --to get column\n \t LEFT OUTER JOIN sys.foreign_keys AS FK \n ON FK.object_id = child.object_id --to get column\n \t LEFT OUTER JOIN sys.extended_properties AS ep\n ON ep.major_id = child.object_id AND class=1\n \t AND ep.name='MS_Description'--the microsoft convention\n WHERE parent.object_id=Object_Id(@tableName,'U') \n \tAND Coalesce(DC.is_system_named,0)+\n Coalesce(KC.is_system_named,0)+\n Coalesce(FK.is_system_named,0)+\n Coalesce(cC.is_system_named,0) = 0\n \t--leave out system-generated constraints\n )\n Go<\/pre>\n\n\n\nThis function is only required for preparing a build, or in generating the script for updating an existing table. I\u2019ve put the table-valued functions in DBO<\/strong> just to keep things simple for the demonstration. You\u2019d obviously deal with them by having a utility schema or dropping them after use. If you cannot have utility iTVFs, even temporarily (nothing wrong with this as we are firmly in development here using development servers), it is possible to \u2018inline\u2019 it as a large batch from an application. I do this inside PowerShell as shown later in the article. Sadly, you can\u2019t create a temporary iTVF as you can with a stored procedure or table.<\/p>\n\n\n\nNow, with this function in place, we can use it to create the JSON file, or data file for the procedure that adds the documentation for the table. When we first run it with a newly-created table, there will be no Extended Properties giving documentation, so that their values will be blank. If you have already added them via SSMS or SQL Doc, they will appear in the list.<\/p>\n\n\n\n
Feed this function the name of a table, and you will get a list of all the columns, indexes and constraints (not the ones with system-generated names- we deliberately leave them out).<\/p>\n\n\n\n
You can now get a list in JSON of all the tables, columns, constraints, and indexes for your database, along with your documentation with just this code which you can save and use as a parameter for the stored procedure that I\u2019ve demonstrated earlier in the article.<\/p>\n\n\n\n
\/* JSON-based (delete the FOR JSON AUTO to get a table)\n Documentation for a single table (dbo.person in our example) *\/\n SELECT Object_Schema_Name(constraints.object_id) + '.'\n + Object_Name(constraints.object_id) AS [table],\n constraints.name AS name, ChildType AS type, Value AS doc\n FROM dbo.ListOutTableEPs('dbo.person') AS constraints\n ORDER BY column_id, childtype\n FOR JSON AUTO;\n \/* JSON-based (delete the FOR JSON AUTO to get a table)\n Documentation for several tables (You'd get all unless you add a \n WHERE clause *\/\n SELECT Object_Schema_Name(constraints.object_id) + '.'\n + Object_Name(constraints.object_id) AS [table],\n constraints.name AS name, ChildType AS type, constraints.value AS doc\n FROM sys.tables AS tbl\n CROSS APPLY dbo.ListOutTableEPs\n \t (Object_Schema_Name(tbl.object_id) + '.' + tbl.name ) AS constraints\n LEFT OUTER JOIN sys.extended_properties AS EP\n ON EP.major_id = tbl.object_id AND EP.minor_id = 0\n WHERE Coalesce(EP.name, '') <> 'microsoft_database_tools_support'\n AND tbl.name NOT LIKE 'sys%'\n ORDER BY tbl.name, constraints.column_id\n FOR JSON AUTO;<\/pre>\n\n\n\nThe #AddOrUpdateTableDocumentation<\/strong> procedure is designed purely to create or update the JSON-based data. It is a bit more complicated than it would be if it was purely intended for use with a build script.<\/p>\n\n\n\nWe\u2019ll use a temporary stored procedure. If we are doing deployments, we don\u2019t want to have anything that leaves an artefact in the database. In this version of the procedure, I\u2019m passing in a table source in a JSON document. If you don\u2019t have JSON, you\u2019ll need to define a Table Valued Parameter type to do the same thing. This will leave an artefact which one wants to avoid in a build script, so it will need subsequent tidying up afterwards. A temporary procedure is very useful for builds and deployments<\/p>\n\n\n\n
-- Drop stored procedure if it already exists\n IF EXISTS\n (\n SELECT *\n FROM tempdb.sys.objects\n WHERE objects.type = 'p'\n AND objects.name LIKE '#AddOrUpdateTableDocumentation%'\n )\n DROP PROCEDURE #AddOrUpdateTableDocumentation;\n GO\n CREATE PROCEDURE #AddOrUpdateTableDocumentation @JSON NVARCHAR(MAX)\n AS\n BEGIN\n SET NOCOUNT ON;\n DECLARE @Documentation TABLE\n (\n TheOrder INT IDENTITY NOT NULL,\n [Table] sysname NOT NULL,\n ChildName sysname NOT NULL,\n ChildType VARCHAR(128) NOT NULL,\n Doc NVARCHAR(4000) NOT NULL\n );\n -- insert into this table variable from the JSON file\n INSERT INTO @Documentation ([Table], ChildName, ChildType, Doc)\n SELECT [Table], ChildName, ChildType, Doc\n FROM\n OpenJson(@JSON)\n WITH\n (\n [Table] sysname '$.table', ChildName sysname '$.name',\n ChildType VARCHAR(128) '$.type', Doc VARCHAR(128) '$.doc'\n )\n WHERE Len(Doc) > 0;;\n --declare the various local variables that we need\n DECLARE @TheChildObjectName sysname, @TheDoc NVARCHAR(4000),\n @TheSchema sysname, @TheTable sysname, @TheChildObjectType VARCHAR(128);\n DECLARE @iiMax INT = @@RowCount; -- get the number we need to do \n WHILE @iiMax >= 1 --do them from the end to the beginning\n --process a row at a time \n BEGIN --put all the row values we want into local variables\n SELECT @TheChildObjectName = [@Documentation].ChildName,\n @TheDoc = [@Documentation].Doc,\n @TheSchema = ParseName([@Documentation].[Table], 2),\n @TheTable = ParseName([@Documentation].[Table], 1),\n @TheChildObjectType = [@Documentation].ChildType\n FROM @Documentation\n WHERE [@Documentation].TheOrder = @iiMax;\n IF Object_Id(@TheSchema + '.' + @TheTable) IS NOT NULL\n BEGIN\n IF NOT EXISTS (\n SELECT *\n FROM sys.fn_listextendedproperty(\n \t\t\t\tN'MS_Description', N'SCHEMA', @TheSchema, N'TABLE',\n \t\t\t\t@TheTable, @TheChildObjectType,@TheChildObjectName )\n \t\t\t ) --if the extended property doesn't exist\n EXEC sys.sp_addextendedproperty @name = N'MS_Description',\n @value = @TheDoc, @level0type = N'SCHEMA',\n @level0name = @TheSchema, @level1type = N'TABLE',\n @level1name = @TheTable, @level2type = @TheChildObjectType,\n @level2name = @TheChildObjectName;\n ELSE -- it needs to be updated\n EXEC sys.sp_updateextendedproperty @name = N'MS_Description',\n @value = @TheDoc, @level0type = N'SCHEMA',\n @level0name = @TheSchema, @level1type = N'TABLE',\n @level1name = @TheTable, @level2type = @TheChildObjectType,\n @level2name = @TheChildObjectName;\n END;\n SELECT @iiMax = @iiMax - 1; --and get the previous row\n END;\n END;\n go<\/pre>\n\n\n\nNow we can add or alter the documentation in the database simply by creating a JSON file that contains the data for any number of tables.<\/p>\n\n\n\n
Generating Documentation Build Scripts by Automation.<\/h2>\n\n\n\n Here is a PowerShell script that generates a script for every table, each in its own file without the Extended Properties. Then, it appends the output, so you end up with a tidier and more readable script. I have used an \u2018inlined\u2019 version of the dbo.ListOutTableEPs<\/strong> iTVF, so there are no dependencies to install before you do the scripting.<\/p>\n\n\n\n<# \n A routine to export all the scripts for all the tables from a database \n to the local client machine in a directory according to the server, database \n and table-name.\n \n #>\n $SourceDatabase = 'MyDevDatabase' #where we take the data and build script from \n $SourceServerName = 'MyDevServer'\n $SourceLogin = '' #Leave blank for Windows authentication\n $directory = 'MyScriptsDirectory' # the directory where you want to store them\n \n \n Trap\n {\n # Handle the error\n $err = $_.Exception\n write-host $err.Message\n while ($err.InnerException)\n {\n $err = $err.InnerException\n write-host $err.Message\n };\n # End the script.\n break\n }\n \n #Load SMO assemblies\n Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality\n set-psdebug -strict\n $ErrorActionPreference = \"stop\" # \n # and some handy constants\n $MS = 'Microsoft.SQLServer'\n $My = \"$MS.Management.Smo\"\n \n # If necessary, create the directories to store the scripts in\n if (-not (Test-Path -PathType Container $directory))\n {\n # we create the scripts directory if it doesn't already exist\n New-Item -ItemType Directory -Force -Path $directory;\n }\n \n #create a connection object as we can reuse this for all the operations\n $mySrcSrvConn = new-object \"$MS.Management.Common.ServerConnection\"\n $mySrcSrvConn.ServerInstance = $SourceServerName\n # is this Windows Authentication or UserID\/Password credentials?\n if ($SourceLogin -ne '') #if no login specified, then it was a windows login\n {\n if (Test-Path -path \"$env:USERPROFILE\\$SourceLogin-$SourceServerName.txt\" \n -PathType leaf)\n { #has already got this set for this login so fetch it\n $encrypted = Get-Content \"$env:USERPROFILE\\$SourceLogin-$SourceServerName.txt\" | \n ConvertTo-SecureString\n $Credentials = New-Object System.Management.Automation.PsCredential($SourceLogin, $encrypted)\n }\n else\n { #hasn't got this set for this login\n $Credentials = get-credential -Credential $SourceLogin\n $Credentials.Password | ConvertFrom-SecureString | Set-Content \n \"$env:USERPROFILE\\$SourceLogin-$SourceServerName.txt\"\n }\n $mySrcSrvConn.LoginSecure = $false\n $mySrcSrvConn.Login = $Credentials.UserName\n $mySrcSrvConn.SecurePassword = $Credentials.Password\n }\n # now log into the server and get the server object\n $SourceServerObject = new-object (\"$My.Server\") $mySrcSrvConn\n \n if ($SourceServerObject.Version -eq $null)\n { Throw \"Can't find the instance $SourceServerName\" }\n $SourceDatabaseObject = $SourceServerObject.Databases[$SourceDatabase]\n if ($SourceDatabaseObject.name -ne $SourceDatabase)\n { Throw \"Can't find the database '$SourceDatabase' in $SourceServerName\" };\n \n $TheScriptDirectory = \"$directory\\$($SourceServerName \n -replace '[\\\\\\\/\\:\\.]', '-')\\$($SourceDatabaseObject.name -replace '[\\\\\\\/\\:\\.]', '-')\"\n if (!(Test-Path -path $TheScriptDirectory))\n {\n Try { New-Item $TheScriptDirectory -type directory | out-null }\n Catch [system.exception]{\n Write-Error \"error while creating '$TheScriptDirectory' \"\n return\n }\n }\n #so now we can create a script of the tables, but first do the options\n $Options = New-Object -TypeName \"$My.ScriptingOptions\"\n #now change whatever you need\n $Options.ExtendedProperties = $false\n $Options.IncludeIfNotExists = $true\n $Options.ScriptBatchTerminator = $true\n $Options.DriAll = $true\n $Options.NoCollation = $true\n $options.permissions=$true\n $Options.filename = ''\n $Options.ToFileOnly = $true\n $Options.Indexes = $true\n $Options.NonClusteredIndexes = $true\n $Options.XmlIndexes = $true\n $Options.IncludeFullTextCatalogRootPath = $true\n \n \n $SourceDatabaseObject.Tables |\n foreach {\n $filename = \"$($_.schema)_$($_.Name)\" -replace '[\\\\\\\/\\:\\.]', '-'\n $Options.filename = \"$TheScriptDirectory\\$filename.sql\"\n $_.Script($options)\n $SQL=\"\n declare @JSON nvarchar(max) =\n (SELECT Object_Schema_Name(constraints.object_id) + '.'\n + Object_Name(constraints.object_id) AS [table],\n constraints.name AS name, ChildType AS type, Value AS doc\n FROM\n (SELECT ---first we do columns\n col.name, --the name of the database thing\n col.object_id,--the object it belongs to\n col.column_id,--the column associated with it - used to sort in column order \n Coalesce(Convert(NVARCHAR(4000), ep.value), '') AS value,--the documentation \n Convert(VARCHAR(20),'column') AS ChildType -- section is about columns\n FROM sys.columns AS col --the columns\n INNER JOIN sys.objects -- associated with the table \n ON objects.object_id = col.object_id \n AND objects.object_id=Object_Id('$($_.schema).$($_.Name)','U') --just the one\n LEFT OUTER JOIN sys.extended_properties AS ep --and pick up any existing documentation\n ON ep.major_id = col.object_id AND ep.class = 1 \n AND ep.minor_id = col.column_id\n AND ep.name='MS_Description' --the microsoft convention\n UNION ALL\n SELECT -- Next we do indexes\n child.name, parent.object_id, 1000, \n Coalesce(Convert(NVARCHAR(100), ep.value), ''), 'Index'\n FROM sys.indexes AS child --indexes are treated in a very similar way \n INNER JOIN sys.objects AS parent\n ON child.object_id = parent.object_id\n LEFT OUTER JOIN sys.extended_properties AS ep\n ON ep.major_id = child.object_id \n AND ep.minor_id = child.index_id AND ep.class = 7\n AND ep.name='MS_Description'--the microsoft convention\n WHERE parent.object_id=Object_Id('$($_.schema).$($_.Name)','U')\n UNION all\n SELECT \n child.name, \n parent.object_id, \n Coalesce(DC.parent_column_id, cC.parent_column_id, 1000), \n Coalesce(Convert(NVARCHAR(100), ep.value), ''),\n CASE WHEN child.type_desc LIKE '%constraint' \n THEN 'constraint' \n ELSE Lower(Replace(child.type_desc,'SQL_','')) end\n FROM sys.objects AS child\n INNER JOIN sys.objects AS parent\n ON child.parent_object_id = parent.object_id\n --we need to gather up information like column and whether\n --they are system-generated (who would want to docuement them\n LEFT OUTER JOIN sys.default_constraints AS DC\n ON DC.object_id = child.object_id -- to get column\n LEFT OUTER JOIN sys.check_constraints AS cC\n ON cC.object_id = child.object_id --to get column\n LEFT OUTER JOIN sys.key_constraints AS KC\n ON KC.object_id = child.object_id --to get column\n LEFT OUTER JOIN sys.foreign_keys AS FK \n ON FK.object_id = child.object_id --to get column\n LEFT OUTER JOIN sys.extended_properties AS ep\n ON ep.major_id = child.object_id AND class=1\n AND ep.name='MS_Description'--the microsoft convention\n WHERE parent.object_id=Object_Id('$($_.schema).$($_.Name)','U') \n AND Coalesce(DC.is_system_named,0)+\n Coalesce(KC.is_system_named,0)+\n Coalesce(FK.is_system_named,0)+\n Coalesce(cC.is_system_named,0) = 0) AS constraints\n ORDER BY column_id, childtype\n FOR JSON AUTO);\n SELECT '\n IF NOT EXISTS\n (\n SELECT * FROM tempdb.INFORMATION_SCHEMA.ROUTINES AS R\n WHERE R.ROUTINE_NAME LIKE ''#AddOrUpdateTableDocumentation%''\n )\n RAISERROR(\n ''You will need to create #AddOrUpdateTableDocumentation to add Extended Properties'',\n 16, 1);\n -- adding Documentation for the table contents \n ELSE\n EXECUTE #AddOrUpdateTableDocumentation '''+Replace(Replace(@JSON,'''','''''')\n ,'},{','},\n {')+'''' AS JSONifiedList\n Select @JSON\n \"\n #execute this script, using the existing connection\n $sqlConnection = new-object System.Data.SqlClient.SqlConnection($mySrcSrvConn.connectionString)\n $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)\n $conn.ExecuteScalar(\"USE $SourceDatabase\")\n add-content -path \"$TheScriptDirectory\\$filename.sql\" -Value ($conn.ExecuteScalar($sql))\n \n }<\/pre>\n\n\n\nIf You Are Using an Older Version of SQL Server or Don\u2019t Like JSON<\/h2>\n\n\n\n You can still add Extended Properties to a build script easily if you can\u2019t use JSON, here is an approach that doesn\u2019t even need a temporary procedure. The downside is that you can\u2019t use it to edit an existing property, just for a build script.<\/p>\n\n\n\n
IF Object_Id('dbo.person','u') IS NOT NULL DROP TABLE dbo.Person\n GO\n CREATE TABLE Person (\n person_ID INT NOT NULL IDENTITY CONSTRAINT PK_PersonID PRIMARY KEY,\n Title NVARCHAR(8) NULL,\n FirstName VARCHAR(40) NOT NULL,\n MiddleName VARCHAR(40) NULL,\n LastName VARCHAR(40) NOT NULL,\n Suffix NVARCHAR(10) NULL,\n fullName AS ( \n Coalesce(Title+' ','')+Firstname+COALESCE(' '+MiddleName,'')\n \t + ' '+Lastname+ COALESCE(' '+suffix,'')) persisted NOT null,\n ModifiedDate DATETIME NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GetDate()\n );\n CREATE NONCLUSTERED INDEX SearchByPersonLastname\n ON Person(LastName ASC, FirstName ASC)\n WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, \n SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, \n \t ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);\n GO\n -- create all the Extended Properties\n DECLARE @TheScript NVARCHAR(MAX) =\n (SELECT 'EXEC sys.sp_addextendedproperty @name = N''MS_Description'', \n @value = N'''+ Replace(Explanation,'''','''''') + ''',\n @level0type = N''SCHEMA'', @level0name = N''' + Replace(theSchema,'''','''''') + ''',\n @level1type = N''TABLE'', @level1name = N''' + Replace(TheTable,'''','''''') + ''', \n @level2type = N''' + Replace(TheChildObject,'''','''''') + ''', \n @level2name = N''' + Replace(ColumnName,'''','''''') + ''';\n ' \n FROM\n (VALUES\n \n ('person_ID','surrogate key to guarantee uniqueness ','dbo','Person','column'),\n ('Title','This would be ''Mr'', ''Sir'', or ''Dr'' for example',\n 'dbo','Person','column'),\n ('FirstName','the Firstname or familiar name','dbo','Person','column'),\n ('MiddleName','optional middle name(s)','dbo','Person','column'),\n ('LastName','the surname, last name or cultural equivalent','dbo',\n 'Person','column'),\n ('Suffix','optional suffix (e.g. ''II'',''D Phil'',''MD'')','dbo','Person','column'),\n ('fullName','this is a computed column created from all the parts of the name',\n 'dbo','Person','column'),\n ('DF_ModifiedDate','this automatically provides the current date','dbo','Person',\n 'constraint'),\n ('ModifiedDate','when modified','dbo','Person','column'),\n ('PK_PersonID','index created by the PK constraint','dbo','Person','Index'),\n ('PK_PersonID','the primary key constraint','dbo','Person','constraint'),\n ('SearchByPersonLastname','the index to allow searches by lastname+ firstname',\n 'dbo','Person','Index')\n \t ) AS Properties(ColumnName, Explanation, theSchema, TheTable,TheChildObject)\n WHERE Coalesce(explanation,'')<>''\n FOR XML PATH (''), TYPE).value('.', 'varchar(max)')\n EXEC sys.sp_executesql @stmt = @TheScript \n --Run time-compiled Transact-SQL statements can expose applications to malicious attacks.<\/pre>\n\n\n\nTo generate this build script from an existing version of the table with the documentation added to the Extended Properties, you can just use this procedure.<\/p>\n\n\n\n
IF Object_Id('dbo.ScriptOutExtendedProperties','IF') IS NOT NULL\n DROP function dbo.ScriptOutExtendedProperties\n GO\n CREATE FUNCTION dbo.ScriptOutExtendedProperties\n \/**\n Summary: >\n This function will script out all Extended Properties\n of the table you specify\n Author: PhilFactor\n Date: 08\/12\/2017\n Parameters: \n - @TableName sysname--the name of the table with optional schema\n - @option --default 'all', has the existing documentation. 'none' leaves them blank \n Examples:\n - Select * from dbo.ScriptOutExtendedProperties('person',Default)\n - Select * from dbo.ScriptOutExtendedProperties('person','none')\n - Select * from dbo.ScriptOutExtendedProperties('humanresources.employee',Default)\n Returns: >\n A one-row table containing a column with the build script\n **\/\n (@TableName sysname, @option VARCHAR(10)='all')\n RETURNS TABLE\n --WITH ENCRYPTION|SCHEMABINDING, ..\n AS\n RETURN\n (\n SELECT\n --we start out by inserting the part of the script that will create a script for\n --each extended property. It will put the results in a variable called '@TheScript'\n '-- create all the Extended Properties\n DECLARE @TheScript NVARCHAR(MAX) =\n (SELECT ''EXEC sys.sp_addextendedproperty @name = N''''MS_Description'''', \n @value = N''''''+ Replace(Explanation,'''''''','''''''''''') + '''''',\n @level0type = N''''SCHEMA'''', @level0name = N'''''' + \n Replace(theSchema,'''''''','''''''''''') + '''''',\n @level1type = N''''TABLE'''', @level1name = N'''''' + \n Replace(TheTable,'''''''','''''''''''') + '''''', \n @level2type = N'''''' + Replace(TheChildObject,'''''''','''''''''''') + '''''', \n @level2name = N'''''' + Replace(ColumnName,'''''''','''''''''''') + '''''';\n '' \n FROM\n (VALUES\n ' +\n --now we create the table source that actually lists all the Extended Properties \n --whether they are filled in or not \n Stuff --\n \t (\n (\n SELECT lines.Thesql\n FROM\n (\n SELECT -- name object_id column_id value\n \t\t\tcolumn_id,\n \t\t\t Descriptions.name AS The_object_name, \n \t\t\t',\n (''' \n \t\t\t + Replace(Descriptions.name, '''', '''''') + ''',''' \n \t\t\t + Replace(Coalesce(Descriptions.value, ''), '''', '''''') + ''',''' \n \t\t\t + Replace(Object_Schema_Name(Descriptions.object_id), '''', '''''')\n + ''',''' \n \t\t\t + Replace(Object_Name(Descriptions.object_id), '''', '''''')+ ''',''' \n \t\t\t + Replace(Descriptions.ChildType, '''', '''''') + ''')' AS Thesql\n FROM dbo.ListOutTableEPs(@TableName) Descriptions \n \t\t\t WHERE Descriptions.value = CASE WHEN @option='all' \n THEN Descriptions.value ELSE '' END\n ) AS lines(column_id, The_object_name, Thesql)\n \t\t ORDER BY lines.column_id, lines.The_object_name\n FOR XML PATH(''), TYPE\n ).value('.', 'varchar(max)'), 1, 1, '')\n --and now we put in the final part of the code that executes the string\n + '\n \t ) AS Properties(ColumnName, Explanation, theSchema, TheTable,TheChildObject)\n WHERE Coalesce(explanation,'''')<>''''\n FOR XML PATH (''''), TYPE).value(''.'', ''varchar(max)'')\n EXEC sys.sp_executesql @stmt = @TheScript \n --Run time-compiled Transact-SQL statements can expose applications to malicious attacks.\n ' AS TheScript\n );\n GO<\/pre>\n\n\n\nWhen you run this script, it will produce the build script that you can append to the output of your table build script to add the documentation as Extended Properties, in the same way we\u2019ve just done in a PowerShell script for the JSON version.<\/p>\n\n\n\n
Conclusions<\/h2>\n\n\n\n The provision of Extended Properties for documentation is typical of the SQL Server features that time forgot, where the SQL Server team occasionally wax enthusiastic about a new feature but give the impression that their attention wanders like a Labrador puppy racing after a ball when you throw it another one. There is so much they could do to improve the feature but, in the meantime, it is up to us to make what there is usable. For me, the top priorities are that documentation for the components of a database must be \u2026<\/p>\n\n\n
\n
\nEasy to put into source control<\/li>\n\n\n\n Easier to see what is there and what is missing<\/li>\n\n\n\n Easier to see what needs changing<\/li>\n\n\n\n Easy to associate them with the build script<\/li>\n<\/ul>\n<\/div>\n\n\nI\u2019d like to see Microsoft improve the feature so that the MS_Description<\/strong> column documentation is introduced into the table build process in SMO. It should be accessible via SSMS as inline comments after the column definition and the table documentation is added to the beginning as block comments. I\u2019d also want the documentation for indexes to be added to the generated scripts of the index definition as comments. It isn\u2019t hard to do and looks great.<\/p>\n\n\n\n\n FAQs: Scripting the Description of Database Tables Using Extended Properties<\/h2>\n\n 1. How do you add extended properties to SQL Server tables and columns?<\/h3>\n \n
Use the sp_addextendedproperty system stored procedure to attach MS_Description values to tables, columns, indexes, and constraints. Specify the property name, value, and object hierarchy (schema, table, column). To update existing properties, use sp_updateextendedproperty. The article provides a JSON-based approach that generates these calls automatically from a structured documentation file.<\/p>\n <\/div>\n
2. What is the best way to version-control SQL Server database documentation?<\/h3>\n \n
Store extended property definitions as JSON files alongside your DDL scripts in source control. The JSON format maps each table and column to its description, making it easy to diff changes, review in pull requests, and generate build scripts automatically.<\/p>\n <\/div>\n
3. Can you use extended properties without JSON on older SQL Server versions?<\/h3>\n \n
Yes. The article includes an alternative approach for pre-2016 SQL Server versions without native JSON functions. This method uses XML or string-based scripting to generate the same sp_addextendedproperty and sp_updateextendedproperty calls.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn to script SQL Server extended properties for table and column documentation using a JSON-based approach. Includes T-SQL functions, automation scripts, and solutions for older SQL Server versions.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143516,143531],"tags":[95509],"coauthors":[6813],"class_list":["post-77452","post","type-post","status-publish","format-standard","hentry","category-database-devops","category-t-sql-programming-sql-server","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77452","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=77452"}],"version-history":[{"count":15,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77452\/revisions"}],"predecessor-version":[{"id":110047,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77452\/revisions\/110047"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77452"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77452"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77452"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77452"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-2.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-3.png\")
<\/figure>\n\n\n\n