You can make all the difference to the ease of the task of developing a database by using Extended Properties as documentation. Why aren’t they universally used? Because they are a pain to script and maintain. I’ve spent too long enduring the difficulties, and I’ve even published a few useful ways of managing Extended Properties, but a recent experience in managing the documentation of tables made me decide finally to try to do something about it. After some rather unsatisfactory but workable approaches, I hit on a JSON-based solution that suits me fine. It is possible to create a solution without JSON or a recent version of SQL Server, and I’ll show you how later on in the article, but it isn’t as neat.
Why Use Extended Properties for Documentation?
Once you’ve got into the habit of using Extended Properties to document your database, there are obvious benefits:
- You can explain why you added that index or modified that constraint.
- You can describe exactly what that rather obscure column does.
- You can add a reasoned explanation to the use of a table.
You will often need these explanations because, sadly, DDL code isn’t ‘self-documenting’, and human memory is fallible. Extended Properties are easily searched because they are all exposed in one system view.
It is great to add explanations to lists of procedures, functions and views once the database becomes sizeable. Extended Properties are useful when exploring the metadata, but the requirement isn’t quite so essential because comments are preserved along with the source code. Tables, however, are a big problem because SQL Server throws away the script that produces the table, along with all the comments. The reason that this happens is that there are many ways you can alter parts of a table without scripting the entire table. How could one infallibly preserve all these ALTER statements in the preserved script? It’s tricky. Table scripts that you get from SSMS or script via SMO are therefore synthesised from the system tables but without those comments or even Extended Properties.
If you’ve added documentation, you get more information when you explore the metadata of a table and can quickly get up to speed with it. With the help of a simple inline table-valued user defined function, for example, you can get useful info. In AdventureWorks, just to demonstrate this, I can use this query (Download the functions and stored procedure required to follow along at the bottom of this article in the section marked ‘downloads’):
|
1 |
Select * from dbo.ListOutTableColumns('[Person].[Address]') order by column_id |
… and get this information.
It is still worth documenting database objects other than tables. If you just rely on embedded comments within procedures, functions and views, you wouldn’t get handy information listed like this. It also means you only have to document the database once and extract reports from it as necessary.
The Problem of Using Extended Properties for Documentation
I’ve experienced four problems for maintaining documentation:
- Writing Extended Properties in the first place
- Maintaining them over time
- Preserving them in source control
- Generating a build script for when you create the table from code, or when you refactor by dropping and replacing the table
I use SQL Doc for the first two tasks, of course, but the third and fourth must be done by scripting them out. Scripting all those Extended Properties can be a nightmare if you’ve been conscientious in creating them.
From these general difficulties, what are the essential tasks that are particularly tricky? In my experience, it is
- Knowing what is and isn’t there in the documentation of your objects in the first place
- Altering several existing Extended Properties, especially when you are doing it in bulk and something gets renamed
- Adding several Extended Properties in a single operation
- Scripting out Extended Properties, either for documentation or for source control
Updating an extended property in SQL for a migration script, for example, can be painful. You would get an error if you updated an extended property that didn’t exist or if you added one that already existed. To get around that, you’d first filter out all those properties that had blank strings and execute either an ADD or UPDATE depending on whether the property already existed. In this example, we update just the documentation for the LastName column on a new table, dbo.Person. (Script included with the article)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
IF Object_Id('dbo.person') IS NOT NULL BEGIN IF not exists(SELECT * from sys.fn_Listextendedproperty ( N'MS_Description', N'SCHEMA', N'dbo', N'TABLE', N'Person', N'column', N'LastName')) EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Persons very last name', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Person', @level2type = N'column', @level2name = N'LastName' ELSE EXEC sys.sp_Updateextendedproperty @name = N'MS_Description', @value = N'Persons very last name', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Person', @level2type = N'column', @level2name = N'LastName' END |
Scripting the Extended Properties
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 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.
Here are three of the Extended Properties, just to show you a flavor
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.', @level0type = N'SCHEMA', @level0name = N'HumanResources', @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'COLUMN', @level2name = N'BusinessEntityID'; GO EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Unique national identification number such as a social security number.', @level0type = N'SCHEMA', @level0name = N'HumanResources', @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'COLUMN', @level2name = N'NationalIDNumber'; GO EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Network login.', @level0type = N'SCHEMA', @level0name = N'HumanResources', @level1type = N'TABLE', @level1name = N'Employee', @level2type = N'COLUMN', @level2name = N'LoginID'; GO |
It 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.
The HumanResources.Employee 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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
IF Object_Id('dbo.person','u') IS NOT NULL DROP TABLE dbo.Person GO CREATE TABLE Person ( person_ID INT NOT NULL IDENTITY CONSTRAINT PK_PersonID PRIMARY KEY, Title NVARCHAR(8) NULL, FirstName VARCHAR(40) NOT NULL, MiddleName VARCHAR(40) NULL, LastName VARCHAR(40) NOT NULL, Suffix NVARCHAR(10) NULL, fullName AS ( Coalesce(Title+' ','')+Firstname+COALESCE(' '+MiddleName,'') + ' '+Lastname+ COALESCE(' '+suffix,'')) persisted NOT null, ModifiedDate DATETIME NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GetDate() ); CREATE NONCLUSTERED INDEX SearchByPersonLastname ON Person(LastName ASC, FirstName ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); GO EXECUTE #AddOrUpdateTableDocumentation '[{"table":"dbo.Person","name":"person_ID","type":"column", "doc":"surrogate key to guarantee uniqueness "}, {"table":"dbo.Person","name":"Title","type":"column", "doc":"This would be ''Mr'', ''Sir'', or ''Dr'' for example"}, {"table":"dbo.Person","name":"FirstName","type":"column", "doc":"the Firstname or familiar name"}, {"table":"dbo.Person","name":"MiddleName","type":"column", "doc":"optional middle name(s)"}, {"table":"dbo.Person","name":"LastName","type":"column", "doc":"the surname, last name or cultural equivalent"}, {"table":"dbo.Person","name":"Suffix","type":"column", "doc":"optional suffix (e.g. ''II'',''D Phil'',''MD'')"}, {"table":"dbo.Person","name":"fullName","type":"column", "doc":"this is a computed column created from all the parts of the name"}, {"table":"dbo.Person","name":"ModifiedDate","type":"column", "doc":"when modified"}, {"table":"dbo.Person","name":"DF_ModifiedDate","type":"constraint", "doc":"this automatically provides the current date"}, {"table":"dbo.Person","name":"PK_PersonID","type":"constraint", "doc":""}, {"table":"dbo.Person","name":"PK_PersonID","type":"Index", "doc":"index created by the PK constraint"}, {"table":"dbo.Person","name":"SearchByPersonLastname","type":"Index", "doc":"the index to allow searches by lastname+ firstname"}]' |
To see how SMS would do the same this, just right-click on the table in the browser pane once you’ve run the code that created the table.
Yes, it is far more difficult to read. More to the point, it doesn’t actually help you to add the documentation. Also, the script that we use via the #AddOrUpdateTableDocumentation 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….
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXECUTE #AddOrUpdateTableDocumentation '[{"table":"dbo.Person","name":"person_ID","type":"column","doc":""}, {"table":"dbo.Person","name":"Title","type":"column","doc":""}, {"table":"dbo.Person","name":"FirstName","type":"column","doc":""}, {"table":"dbo.Person","name":"MiddleName","type":"column","doc":""}, {"table":"dbo.Person","name":"LastName","type":"column","doc":""}, {"table":"dbo.Person","name":"Suffix","type":"column","doc":""}, {"table":"dbo.Person","name":"fullName","type":"column","doc":""}, {"table":"dbo.Person","name":"ModifiedDate","type":"column","doc":""}, {"table":"dbo.Person","name":"DF_ModifiedDate","type":"constraint","doc":""}, {"table":"dbo.Person","name":"PK_PersonID","type":"constraint","doc":""}, {"table":"dbo.Person","name":"PK_PersonID","type":"Index","doc":""}, {"table":"dbo.Person","name":"SearchByPersonLastname","type":"Index","doc":""}]' |
You’d then want to fill in all those blank values for “doc”:”” as I did in the first listing. It doesn’t take long! If a doc field is left blank, it isn’t touched.
Did 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.
First, before I show you the function to do this, I need a utility function, ListOutTableEPs, that provides all the details of the columns, constraints and indexes for a table. You’ll see that there are certain subtleties: For example, you don’t want to script out documentation for indexes or constraints that are system-named, do you?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
CREATE FUNCTION dbo.ListOutTableEPs /** Summary: > List out all the constraints, indexes, columns and triggers of a table in column order Author: Phil Factor Date: 12/12/2017 Database: PhilFactor Examples: - Select * from dbo.ListOutTableEPs('dbo.person') - > SELECT Object_Schema_Name(constraints.object_id) + '.' + Object_Name(constraints.object_id) AS [table], constraints.name AS name, ChildType AS type, Value AS doc FROM dbo.ListOutTableEPs('dbo.person') AS constraints ORDER BY column_id, childtype FOR JSON AUTO; Returns: > A table listing all the EPs of type MS_Description if any applied to the children of the table name, object_id, column_id, value note that the column_id is only relevant for columns and column-based constraints. It is mostly used for conveniently ordering the JSON result the same as the build script ChildType can be COLUMN, CONSTRAINT, INDEX, (not yet TRIGGER, and NULL). **/ ( @Tablename NVarchar(100) ) RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, .. AS RETURN ( SELECT ---first we do columns col.name, --the name of the database thing col.object_id,--the object it belongs to col.column_id,--the column associated with it - used to sort in column order Coalesce(Convert(NVARCHAR(4000), ep.value), '') AS value,--the documentation Convert(VARCHAR(20),'column') AS ChildType -- section is about columns FROM sys.columns AS col --the columns INNER JOIN sys.objects -- associated with the table ON objects.object_id = col.object_id AND objects.object_id=Object_Id(@tableName,'U') --just the one LEFT OUTER JOIN sys.extended_properties AS ep --and pick up any existing documentation ON ep.major_id = col.object_id AND ep.class = 1 AND ep.minor_id = col.column_id AND ep.name='MS_Description' --the microsoft convention UNION ALL SELECT -- Next we do indexes child.name, parent.object_id, 1000, Coalesce(Convert(NVARCHAR(100), ep.value), ''), 'Index' FROM sys.indexes AS child --indexes are treated in a very similar way INNER JOIN sys.objects AS parent ON child.object_id = parent.object_id LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = child.object_id AND ep.minor_id = child.index_id AND ep.class = 7 AND ep.name='MS_Description'--the microsoft convention WHERE parent.object_id=Object_Id(@tableName,'U') UNION all SELECT child.name, parent.object_id, Coalesce(DC.parent_column_id, cC.parent_column_id, 1000), Coalesce(Convert(NVARCHAR(100), ep.value), ''), CASE WHEN child.type_desc LIKE '%constraint' THEN 'constraint' ELSE Lower(Replace(child.type_desc,'SQL_','')) end FROM sys.objects AS child INNER JOIN sys.objects AS parent ON child.parent_object_id = parent.object_id --we need to gather up information like column and whether --they are system-generated (who would want to docuement them LEFT OUTER JOIN sys.default_constraints AS DC ON DC.object_id = child.object_id -- to get column LEFT OUTER JOIN sys.check_constraints AS cC ON cC.object_id = child.object_id --to get column LEFT OUTER JOIN sys.key_constraints AS KC ON KC.object_id = child.object_id --to get column LEFT OUTER JOIN sys.foreign_keys AS FK ON FK.object_id = child.object_id --to get column LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = child.object_id AND class=1 AND ep.name='MS_Description'--the microsoft convention WHERE parent.object_id=Object_Id(@tableName,'U') AND Coalesce(DC.is_system_named,0)+ Coalesce(KC.is_system_named,0)+ Coalesce(FK.is_system_named,0)+ Coalesce(cC.is_system_named,0) = 0 --leave out system-generated constraints ) Go |
This function is only required for preparing a build, or in generating the script for updating an existing table. I’ve put the table-valued functions in DBO just to keep things simple for the demonstration. You’d 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 ‘inline’ it as a large batch from an application. I do this inside PowerShell as shown later in the article. Sadly, you can’t create a temporary iTVF as you can with a stored procedure or table.
Now, 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.
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).
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’ve demonstrated earlier in the article.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/* JSON-based (delete the FOR JSON AUTO to get a table) Documentation for a single table (dbo.person in our example) */ SELECT Object_Schema_Name(constraints.object_id) + '.' + Object_Name(constraints.object_id) AS [table], constraints.name AS name, ChildType AS type, Value AS doc FROM dbo.ListOutTableEPs('dbo.person') AS constraints ORDER BY column_id, childtype FOR JSON AUTO; /* JSON-based (delete the FOR JSON AUTO to get a table) Documentation for several tables (You'd get all unless you add a WHERE clause */ SELECT Object_Schema_Name(constraints.object_id) + '.' + Object_Name(constraints.object_id) AS [table], constraints.name AS name, ChildType AS type, constraints.value AS doc FROM sys.tables AS tbl CROSS APPLY dbo.ListOutTableEPs (Object_Schema_Name(tbl.object_id) + '.' + tbl.name ) AS constraints LEFT OUTER JOIN sys.extended_properties AS EP ON EP.major_id = tbl.object_id AND EP.minor_id = 0 WHERE Coalesce(EP.name, '') <> 'microsoft_database_tools_support' AND tbl.name NOT LIKE 'sys%' ORDER BY tbl.name, constraints.column_id FOR JSON AUTO; |
The #AddOrUpdateTableDocumentation 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.
We’ll use a temporary stored procedure. If we are doing deployments, we don’t want to have anything that leaves an artefact in the database. In this version of the procedure, I’m passing in a table source in a JSON document. If you don’t have JSON, you’ll 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
-- Drop stored procedure if it already exists IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE objects.type = 'p' AND objects.name LIKE '#AddOrUpdateTableDocumentation%' ) DROP PROCEDURE #AddOrUpdateTableDocumentation; GO CREATE PROCEDURE #AddOrUpdateTableDocumentation @JSON NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @Documentation TABLE ( TheOrder INT IDENTITY NOT NULL, [Table] sysname NOT NULL, ChildName sysname NOT NULL, ChildType VARCHAR(128) NOT NULL, Doc NVARCHAR(4000) NOT NULL ); -- insert into this table variable from the JSON file INSERT INTO @Documentation ([Table], ChildName, ChildType, Doc) SELECT [Table], ChildName, ChildType, Doc FROM OpenJson(@JSON) WITH ( [Table] sysname '$.table', ChildName sysname '$.name', ChildType VARCHAR(128) '$.type', Doc VARCHAR(128) '$.doc' ) WHERE Len(Doc) > 0;; --declare the various local variables that we need DECLARE @TheChildObjectName sysname, @TheDoc NVARCHAR(4000), @TheSchema sysname, @TheTable sysname, @TheChildObjectType VARCHAR(128); DECLARE @iiMax INT = @@RowCount; -- get the number we need to do WHILE @iiMax >= 1 --do them from the end to the beginning --process a row at a time BEGIN --put all the row values we want into local variables SELECT @TheChildObjectName = [@Documentation].ChildName, @TheDoc = [@Documentation].Doc, @TheSchema = ParseName([@Documentation].[Table], 2), @TheTable = ParseName([@Documentation].[Table], 1), @TheChildObjectType = [@Documentation].ChildType FROM @Documentation WHERE [@Documentation].TheOrder = @iiMax; IF Object_Id(@TheSchema + '.' + @TheTable) IS NOT NULL BEGIN IF NOT EXISTS ( SELECT * FROM sys.fn_listextendedproperty( N'MS_Description', N'SCHEMA', @TheSchema, N'TABLE', @TheTable, @TheChildObjectType,@TheChildObjectName ) ) --if the extended property doesn't exist EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = @TheDoc, @level0type = N'SCHEMA', @level0name = @TheSchema, @level1type = N'TABLE', @level1name = @TheTable, @level2type = @TheChildObjectType, @level2name = @TheChildObjectName; ELSE -- it needs to be updated EXEC sys.sp_updateextendedproperty @name = N'MS_Description', @value = @TheDoc, @level0type = N'SCHEMA', @level0name = @TheSchema, @level1type = N'TABLE', @level1name = @TheTable, @level2type = @TheChildObjectType, @level2name = @TheChildObjectName; END; SELECT @iiMax = @iiMax - 1; --and get the previous row END; END; go |
Now 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.
Generating Documentation Build Scripts by Automation.
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 ‘inlined’ version of the dbo.ListOutTableEPs iTVF, so there are no dependencies to install before you do the scripting.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
<# A routine to export all the scripts for all the tables from a database to the local client machine in a directory according to the server, database and table-name. #> $SourceDatabase = 'MyDevDatabase' #where we take the data and build script from $SourceServerName = 'MyDevServer' $SourceLogin = '' #Leave blank for Windows authentication $directory = 'MyScriptsDirectory' # the directory where you want to store them Trap { # Handle the error $err = $_.Exception write-host $err.Message while ($err.InnerException) { $err = $err.InnerException write-host $err.Message }; # End the script. break } #Load SMO assemblies Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality set-psdebug -strict $ErrorActionPreference = "stop" # # and some handy constants $MS = 'Microsoft.SQLServer' $My = "$MS.Management.Smo" # If necessary, create the directories to store the scripts in if (-not (Test-Path -PathType Container $directory)) { # we create the scripts directory if it doesn't already exist New-Item -ItemType Directory -Force -Path $directory; } #create a connection object as we can reuse this for all the operations $mySrcSrvConn = new-object "$MS.Management.Common.ServerConnection" $mySrcSrvConn.ServerInstance = $SourceServerName # is this Windows Authentication or UserID/Password credentials? if ($SourceLogin -ne '') #if no login specified, then it was a windows login { if (Test-Path -path "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt" -PathType leaf) { #has already got this set for this login so fetch it $encrypted = Get-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt" | ConvertTo-SecureString $Credentials = New-Object System.Management.Automation.PsCredential($SourceLogin, $encrypted) } else { #hasn't got this set for this login $Credentials = get-credential -Credential $SourceLogin $Credentials.Password | ConvertFrom-SecureString | Set-Content "$env:USERPROFILE\$SourceLogin-$SourceServerName.txt" } $mySrcSrvConn.LoginSecure = $false $mySrcSrvConn.Login = $Credentials.UserName $mySrcSrvConn.SecurePassword = $Credentials.Password } # now log into the server and get the server object $SourceServerObject = new-object ("$My.Server") $mySrcSrvConn if ($SourceServerObject.Version -eq $null) { Throw "Can't find the instance $SourceServerName" } $SourceDatabaseObject = $SourceServerObject.Databases[$SourceDatabase] if ($SourceDatabaseObject.name -ne $SourceDatabase) { Throw "Can't find the database '$SourceDatabase' in $SourceServerName" }; $TheScriptDirectory = "$directory\$($SourceServerName -replace '[\\\/\:\.]', '-')\$($SourceDatabaseObject.name -replace '[\\\/\:\.]', '-')" if (!(Test-Path -path $TheScriptDirectory)) { Try { New-Item $TheScriptDirectory -type directory | out-null } Catch [system.exception]{ Write-Error "error while creating '$TheScriptDirectory' " return } } #so now we can create a script of the tables, but first do the options $Options = New-Object -TypeName "$My.ScriptingOptions" #now change whatever you need $Options.ExtendedProperties = $false $Options.IncludeIfNotExists = $true $Options.ScriptBatchTerminator = $true $Options.DriAll = $true $Options.NoCollation = $true $options.permissions=$true $Options.filename = '' $Options.ToFileOnly = $true $Options.Indexes = $true $Options.NonClusteredIndexes = $true $Options.XmlIndexes = $true $Options.IncludeFullTextCatalogRootPath = $true $SourceDatabaseObject.Tables | foreach { $filename = "$($_.schema)_$($_.Name)" -replace '[\\\/\:\.]', '-' $Options.filename = "$TheScriptDirectory\$filename.sql" $_.Script($options) $SQL=" declare @JSON nvarchar(max) = (SELECT Object_Schema_Name(constraints.object_id) + '.' + Object_Name(constraints.object_id) AS [table], constraints.name AS name, ChildType AS type, Value AS doc FROM (SELECT ---first we do columns col.name, --the name of the database thing col.object_id,--the object it belongs to col.column_id,--the column associated with it - used to sort in column order Coalesce(Convert(NVARCHAR(4000), ep.value), '') AS value,--the documentation Convert(VARCHAR(20),'column') AS ChildType -- section is about columns FROM sys.columns AS col --the columns INNER JOIN sys.objects -- associated with the table ON objects.object_id = col.object_id AND objects.object_id=Object_Id('$($_.schema).$($_.Name)','U') --just the one LEFT OUTER JOIN sys.extended_properties AS ep --and pick up any existing documentation ON ep.major_id = col.object_id AND ep.class = 1 AND ep.minor_id = col.column_id AND ep.name='MS_Description' --the microsoft convention UNION ALL SELECT -- Next we do indexes child.name, parent.object_id, 1000, Coalesce(Convert(NVARCHAR(100), ep.value), ''), 'Index' FROM sys.indexes AS child --indexes are treated in a very similar way INNER JOIN sys.objects AS parent ON child.object_id = parent.object_id LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = child.object_id AND ep.minor_id = child.index_id AND ep.class = 7 AND ep.name='MS_Description'--the microsoft convention WHERE parent.object_id=Object_Id('$($_.schema).$($_.Name)','U') UNION all SELECT child.name, parent.object_id, Coalesce(DC.parent_column_id, cC.parent_column_id, 1000), Coalesce(Convert(NVARCHAR(100), ep.value), ''), CASE WHEN child.type_desc LIKE '%constraint' THEN 'constraint' ELSE Lower(Replace(child.type_desc,'SQL_','')) end FROM sys.objects AS child INNER JOIN sys.objects AS parent ON child.parent_object_id = parent.object_id --we need to gather up information like column and whether --they are system-generated (who would want to docuement them LEFT OUTER JOIN sys.default_constraints AS DC ON DC.object_id = child.object_id -- to get column LEFT OUTER JOIN sys.check_constraints AS cC ON cC.object_id = child.object_id --to get column LEFT OUTER JOIN sys.key_constraints AS KC ON KC.object_id = child.object_id --to get column LEFT OUTER JOIN sys.foreign_keys AS FK ON FK.object_id = child.object_id --to get column LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = child.object_id AND class=1 AND ep.name='MS_Description'--the microsoft convention WHERE parent.object_id=Object_Id('$($_.schema).$($_.Name)','U') AND Coalesce(DC.is_system_named,0)+ Coalesce(KC.is_system_named,0)+ Coalesce(FK.is_system_named,0)+ Coalesce(cC.is_system_named,0) = 0) AS constraints ORDER BY column_id, childtype FOR JSON AUTO); SELECT ' IF NOT EXISTS ( SELECT * FROM tempdb.INFORMATION_SCHEMA.ROUTINES AS R WHERE R.ROUTINE_NAME LIKE ''#AddOrUpdateTableDocumentation%'' ) RAISERROR( ''You will need to create #AddOrUpdateTableDocumentation to add Extended Properties'', 16, 1); -- adding Documentation for the table contents ELSE EXECUTE #AddOrUpdateTableDocumentation '''+Replace(Replace(@JSON,'''','''''') ,'},{','}, {')+'''' AS JSONifiedList Select @JSON " #execute this script, using the existing connection $sqlConnection = new-object System.Data.SqlClient.SqlConnection($mySrcSrvConn.connectionString) $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection) $conn.ExecuteScalar("USE $SourceDatabase") add-content -path "$TheScriptDirectory\$filename.sql" -Value ($conn.ExecuteScalar($sql)) } |
If You Are Using an Older Version of SQL Server or Don’t Like JSON
You can still add Extended Properties to a build script easily if you can’t use JSON, here is an approach that doesn’t even need a temporary procedure. The downside is that you can’t use it to edit an existing property, just for a build script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
IF Object_Id('dbo.person','u') IS NOT NULL DROP TABLE dbo.Person GO CREATE TABLE Person ( person_ID INT NOT NULL IDENTITY CONSTRAINT PK_PersonID PRIMARY KEY, Title NVARCHAR(8) NULL, FirstName VARCHAR(40) NOT NULL, MiddleName VARCHAR(40) NULL, LastName VARCHAR(40) NOT NULL, Suffix NVARCHAR(10) NULL, fullName AS ( Coalesce(Title+' ','')+Firstname+COALESCE(' '+MiddleName,'') + ' '+Lastname+ COALESCE(' '+suffix,'')) persisted NOT null, ModifiedDate DATETIME NOT NULL CONSTRAINT DF_ModifiedDate DEFAULT GetDate() ); CREATE NONCLUSTERED INDEX SearchByPersonLastname ON Person(LastName ASC, FirstName ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); GO -- create all the Extended Properties DECLARE @TheScript NVARCHAR(MAX) = (SELECT 'EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N'''+ Replace(Explanation,'''','''''') + ''', @level0type = N''SCHEMA'', @level0name = N''' + Replace(theSchema,'''','''''') + ''', @level1type = N''TABLE'', @level1name = N''' + Replace(TheTable,'''','''''') + ''', @level2type = N''' + Replace(TheChildObject,'''','''''') + ''', @level2name = N''' + Replace(ColumnName,'''','''''') + '''; ' FROM (VALUES ('person_ID','surrogate key to guarantee uniqueness ','dbo','Person','column'), ('Title','This would be ''Mr'', ''Sir'', or ''Dr'' for example', 'dbo','Person','column'), ('FirstName','the Firstname or familiar name','dbo','Person','column'), ('MiddleName','optional middle name(s)','dbo','Person','column'), ('LastName','the surname, last name or cultural equivalent','dbo', 'Person','column'), ('Suffix','optional suffix (e.g. ''II'',''D Phil'',''MD'')','dbo','Person','column'), ('fullName','this is a computed column created from all the parts of the name', 'dbo','Person','column'), ('DF_ModifiedDate','this automatically provides the current date','dbo','Person', 'constraint'), ('ModifiedDate','when modified','dbo','Person','column'), ('PK_PersonID','index created by the PK constraint','dbo','Person','Index'), ('PK_PersonID','the primary key constraint','dbo','Person','constraint'), ('SearchByPersonLastname','the index to allow searches by lastname+ firstname', 'dbo','Person','Index') ) AS Properties(ColumnName, Explanation, theSchema, TheTable,TheChildObject) WHERE Coalesce(explanation,'')<>'' FOR XML PATH (''), TYPE).value('.', 'varchar(max)') EXEC sys.sp_executesql @stmt = @TheScript --Run time-compiled Transact-SQL statements can expose applications to malicious attacks. |
To 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
IF Object_Id('dbo.ScriptOutExtendedProperties','IF') IS NOT NULL DROP function dbo.ScriptOutExtendedProperties GO CREATE FUNCTION dbo.ScriptOutExtendedProperties /** Summary: > This function will script out all Extended Properties of the table you specify Author: PhilFactor Date: 08/12/2017 Parameters: - @TableName sysname--the name of the table with optional schema - @option --default 'all', has the existing documentation. 'none' leaves them blank Examples: - Select * from dbo.ScriptOutExtendedProperties('person',Default) - Select * from dbo.ScriptOutExtendedProperties('person','none') - Select * from dbo.ScriptOutExtendedProperties('humanresources.employee',Default) Returns: > A one-row table containing a column with the build script **/ (@TableName sysname, @option VARCHAR(10)='all') RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, .. AS RETURN ( SELECT --we start out by inserting the part of the script that will create a script for --each extended property. It will put the results in a variable called '@TheScript' '-- create all the Extended Properties DECLARE @TheScript NVARCHAR(MAX) = (SELECT ''EXEC sys.sp_addextendedproperty @name = N''''MS_Description'''', @value = N''''''+ Replace(Explanation,'''''''','''''''''''') + '''''', @level0type = N''''SCHEMA'''', @level0name = N'''''' + Replace(theSchema,'''''''','''''''''''') + '''''', @level1type = N''''TABLE'''', @level1name = N'''''' + Replace(TheTable,'''''''','''''''''''') + '''''', @level2type = N'''''' + Replace(TheChildObject,'''''''','''''''''''') + '''''', @level2name = N'''''' + Replace(ColumnName,'''''''','''''''''''') + ''''''; '' FROM (VALUES ' + --now we create the table source that actually lists all the Extended Properties --whether they are filled in or not Stuff -- ( ( SELECT lines.Thesql FROM ( SELECT -- name object_id column_id value column_id, Descriptions.name AS The_object_name, ', (''' + Replace(Descriptions.name, '''', '''''') + ''',''' + Replace(Coalesce(Descriptions.value, ''), '''', '''''') + ''',''' + Replace(Object_Schema_Name(Descriptions.object_id), '''', '''''') + ''',''' + Replace(Object_Name(Descriptions.object_id), '''', '''''')+ ''',''' + Replace(Descriptions.ChildType, '''', '''''') + ''')' AS Thesql FROM dbo.ListOutTableEPs(@TableName) Descriptions WHERE Descriptions.value = CASE WHEN @option='all' THEN Descriptions.value ELSE '' END ) AS lines(column_id, The_object_name, Thesql) ORDER BY lines.column_id, lines.The_object_name FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 1, '') --and now we put in the final part of the code that executes the string + ' ) AS Properties(ColumnName, Explanation, theSchema, TheTable,TheChildObject) WHERE Coalesce(explanation,'''')<>'''' FOR XML PATH (''''), TYPE).value(''.'', ''varchar(max)'') EXEC sys.sp_executesql @stmt = @TheScript --Run time-compiled Transact-SQL statements can expose applications to malicious attacks. ' AS TheScript ); GO |
When 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’ve just done in a PowerShell script for the JSON version.
Conclusions
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 …
- Easy to put into source control
- Easier to see what is there and what is missing
- Easier to see what needs changing
- Easy to associate them with the build script
I’d like to see Microsoft improve the feature so that the MS_Description 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’d also want the documentation for indexes to be added to the generated scripts of the index definition as comments. It isn’t hard to do and looks great.
Load comments