Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Auto Generate Your Database Documentation

By Andy Jones,

“A society grows great when old men plant trees whose shade they know they shall never sit in.” - Anonymous Greek Proverb.

Introduction

Have you ever connected to SQL Server and been overwhelmed by the volume of databases and objects and had no clue as to what each database was for or the purpose of that cryptically named table? As a consultant visiting new client sites, this happens to me all the time and it can take some time to become familiar with a new environment and to establish the purpose of everything. This article concerns documenting everything you add to a database using SQL Server extended properties. This is a fantastic time management technique, allowing you to answer questions about the database design once using a central consistent methodology. It also allows the poor DBA who has to pick up your work a valuable insight when you are no longer around to sit in the shade of the tree.

Benefits

Some of the benefits of documenting your database in this way are:

  1. Communicate: If one developer questions you about database design, it is likely others will require similar clarification. Document what you are doing to communicate clearly with others.
  2. Documentation: Do you have to document your database for internal/external regulatory purposes? Add extended properties and then auto-generate a professional repeatable document via a third party tool.
  3. Information: I use the fantastic SQL Prompt from Redgate. These extended properties are exposed via tool tips in SQL Prompt. Hover your mouse in SSMS and get immediate relevant information about the object you are working with - a real time saver.
  4. Portability: your documentation is within the database itself and is backed up with the database. Your documentation travels with the schema wherever that database is restored.

Examples

Some examples of adding extended properties I have found particularly useful are:

Restored Databases

If you have cause to restore databases to alternative development/test/staging servers it can be extremely useful to have some information about that database immediately to hand via a tooltip in management studio. I know you don’t restore production databases in such a manner without obfuscating sensitive data, so I won’t even mention that.

USE [master];
GO
DECLARE @LastFullBackup NVARCHAR(100) = N'C:\SQLServerBackup\OFFICE\DBA\FULL\OFFICE_DBA_FULL_20160211_143456.bak';
DECLARE @DatabaseName sysname = N'DBA';

--back up the tail of the log
BACKUP LOG @DatabaseName TO  DISK = N'C:\SQLServerBackup\OFFICE\DBA\LOG\DBA_LogBackup_2016-02-11_14-35-10.bak'
WITH NOFORMAT, NOINIT,  NAME = N'DBA_LogBackup_2016-02-11_14-35-10', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5;

RESTORE DATABASE @DatabaseName FROM  DISK = N'C:\SQLServerBackup\OFFICE\DBA\FULL\OFFICE_DBA_FULL_20160211_143456.bak'
WITH  FILE = 1,  NOUNLOAD,  STATS = 5, RECOVERY;

USE [DBA];

DECLARE @value NVARCHAR(3000) = @DatabaseName
   + ' test database restored from ' + @LastFullBackup + ' by ' + SYSTEM_USER
   + ' at ' + CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126);

EXECUTE sys.sp_addextendedproperty @name = 'MS_Description', @value = @val

Now, when you hover your mouse in SSMS, you get immediate pertinent information via SQL Prompt:

This immediately tells you who performed the restore using which backup file and when it was done.

Third party Tools

A good DBA is continuously learning new techniques and will accumulate knowledge and scripts from others. I was recently doing my long run while training for the 2016 London marathon contemplating the similarities between getting faster at running and performance tuning SQL Server (an article for another day) when I was listening to the compañero Robert Verell on the SQL Data Partners Podcast. His db_developer role is now in my environment and has been credited via:

DECLARE @db_developer_extended_property SQL_VARIANT;

SET @db_developer_extended_property = SYSTEM_USER + N' ' + CONVERT(CHAR(23),CURRENT_TIMESTAMP,126) + N': do not add developers to the db_owner role as this gives them far too many implicit permissions, add them to the db_developer role instead - see http://sqlcowbell.com/wordpress/why-nobody-ever-needs-the-db_owner-role/'

EXECUTE sys.sp_addextendedproperty @name = 'MS_Description', -- sysname
   @value = @db_developer_extended_property, -- sql_variant
   @level0type = 'USER', -- varchar(128)
   @level0name = 'db_developer', -- sysname
   @level1type = NULL, -- varchar(128)
   @level1name = NULL, -- sysname
   @level2type = NULL, -- varchar(128)
   @level2name = NULL; -- sysname

When the next DBA sees this strange role in their database, they now have full knowledge of why it is there and also a link to investigate for themselves.

One of my favourite sessions while attending PASS 2015 was Steve Wake’s Date Dimension: Past and Future in One Script. Steve’s script to generate the date dimension with all the date attributes you could need already contains extended properties to fully self-document this excellent table. As an aside, I hope Steve is looking forward to the proposal to fix the date of Easter, which may make his life a little easier.

Generate missing extended properties

The following set-based script will generate the statements to create missing extended properties. For brevity, this script demonstrates extended property creation and has been tested for the following object types:

  • CHECK_CONSTRAINT
  • COLUMN
  • FOREIGN_KEY_CONSTRAINT
  • PRIMARY_KEY_CONSTRAINT
  • SQL_INLINE_TABLE_VALUED_FUNCTION
  • SQL_SCALAR_FUNCTION
  • SQL_STORED_PROCEDURE
  • UNIQUE_CONSTRAINT
  • USER_TABLE
  • INDEX

This could easily be extended by inserting the relevant 7 rows into @Parameter for any particular object type that supports extended properties.

  • This script will not create the extended properties, but auto generate the commands to do so. The actual value (@value parameter) of the extended property still has to be manually input.
  • It is possible to automate a description such as “this is column A on table dbo.B” but you will end up with some meaningless documentation that tells the reader very little. I wouldn’t recommend doing this.
  • The script will automatically add the user and date as a prefix of the extended property value, remove or edit as desired. This extended property will inform when the object was created and by whom. It is possible to query the database meta-data for this information, but that only tells you the last time it was created. It is possible it has previously been dropped and re-created as part of a change.
  • Adds extended properties named MS_Descripton for automatic inclusion in our third party documentation generator.

  /*
Name:
(C) Andy Jones
mailto:andrew@aejsoftware.co.uk

Example usage: -

Connect SSMS to the database in which you wish to create extended properties and hit F5.

Description: -
This script will not create the extended properties, but auto generate the commands to do so.
The actual value (@value parameter) of the extended property still has to be manually input.
This script will automatically add the user and date to the extended property value.

Change History: -
1.0 25/11/2015 Created.
*/
DECLARE @Parameter TABLE
   (
     [type_desc] sysname NOT NULL ,
     parameter sysname NOT NULL ,
     val NVARCHAR(100) NOT NULL ,
     PRIMARY KEY ( [type_desc], parameter )
   );


/*set up the data for each object type specifying the correct value for each parameter.*/
INSERT  INTO @Parameter
       ( [type_desc], parameter, val )
VALUES  ( N'CHECK_CONSTRAINT', N'value', N'_replace_value' ),
       ( N'CHECK_CONSTRAINT', N'level0type', N'SCHEMA' ),
       ( N'CHECK_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
       ( N'CHECK_CONSTRAINT', N'level1type', N'TABLE' ),
       ( N'CHECK_CONSTRAINT', N'level1name', N'_replace_parentname' ),
       ( N'CHECK_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
       ( N'CHECK_CONSTRAINT', N'level2name', N'_replace_name' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'value', N'_replace_value' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level0type', N'SCHEMA' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level1type', N'TABLE' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level1name', N'_replace_parentname' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
       ( N'FOREIGN_KEY_CONSTRAINT', N'level2name', N'_replace_name' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'value', N'_replace_value' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level0type', N'SCHEMA' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level1type', N'TABLE' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level1name', N'_replace_parentname' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
       ( N'PRIMARY_KEY_CONSTRAINT', N'level2name', N'_replace_name' ),
       ( N'UNIQUE_CONSTRAINT', N'value', N'_replace_value' ),
       ( N'UNIQUE_CONSTRAINT', N'level0type', N'SCHEMA' ),
       ( N'UNIQUE_CONSTRAINT', N'level0name', N'_replace_schemaname' ),
       ( N'UNIQUE_CONSTRAINT', N'level1type', N'TABLE' ),
       ( N'UNIQUE_CONSTRAINT', N'level1name', N'_replace_parentname' ),
       ( N'UNIQUE_CONSTRAINT', N'level2type', N'CONSTRAINT' ),
       ( N'UNIQUE_CONSTRAINT', N'level2name', N'_replace_name' ),
       ( N'SQL_STORED_PROCEDURE', N'value', N'_replace_value' ),
       ( N'SQL_STORED_PROCEDURE', N'level0type', N'SCHEMA' ),
       ( N'SQL_STORED_PROCEDURE', N'level0name', N'_replace_schemaname' ),
       ( N'SQL_STORED_PROCEDURE', N'level1type', N'PROCEDURE' ),
       ( N'SQL_STORED_PROCEDURE', N'level1name', N'_replace_name' ),
       ( N'SQL_STORED_PROCEDURE', N'level2type', N'NULL' ),
       ( N'SQL_STORED_PROCEDURE', N'level2name', N'NULL' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'value', N'_replace_value' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level0type', N'SCHEMA' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level0name',
         N'_replace_schemaname' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level1type', N'FUNCTION' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level1name', N'_replace_name' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level2type', N'NULL' ),
       ( N'SQL_INLINE_TABLE_VALUED_FUNCTION', N'level2name', N'NULL' ),
       ( N'SQL_SCALAR_FUNCTION', N'value', N'_replace_value' ),
       ( N'SQL_SCALAR_FUNCTION', N'level0type', N'SCHEMA' ),
       ( N'SQL_SCALAR_FUNCTION', N'level0name', N'_replace_schemaname' ),
       ( N'SQL_SCALAR_FUNCTION', N'level1type', N'FUNCTION' ),
       ( N'SQL_SCALAR_FUNCTION', N'level1name', N'_replace_name' ),
       ( N'SQL_SCALAR_FUNCTION', N'level2type', N'NULL' ),
       ( N'SQL_SCALAR_FUNCTION', N'level2name', N'NULL' ),
       ( N'USER_TABLE', N'value', N'_replace_value' ),
       ( N'USER_TABLE', N'level0type', N'SCHEMA' ),
       ( N'USER_TABLE', N'level0name', N'_replace_schemaname' ),
       ( N'USER_TABLE', N'level1type', N'TABLE' ),
       ( N'USER_TABLE', N'level1name', N'_replace_name' ),
       ( N'USER_TABLE', N'level2type', N'NULL' ),
       ( N'USER_TABLE', N'level2name', N'NULL' ),
       ( N'INDEX', N'value', N'_replace_value' ),
       ( N'INDEX', N'level0type', N'SCHEMA' ),
       ( N'INDEX', N'level0name', N'_replace_schemaname' ),
       ( N'INDEX', N'level1type', N'TABLE' ),
       ( N'INDEX', N'level1name', N'_replace_parentname' ),
       ( N'INDEX', N'level2type', N'INDEX' ),
       ( N'INDEX', N'level2name', N'_replace_name' ),
       ( N'COLUMN', N'value', N'_replace_value' ),
       ( N'COLUMN', N'level0type', N'SCHEMA' ),
       ( N'COLUMN', N'level0name', N'_replace_schemaname' ),
       ( N'COLUMN', N'level1type', N'TABLE' ),
       ( N'COLUMN', N'level1name', N'_replace_parentname' ),
       ( N'COLUMN', N'level2type', N'COLUMN' ),
       ( N'COLUMN', N'level2name', N'_replace_name' );


WITH    Obj /*union all objects on which to create extended properties. Objects, columns and indexes.*/
         AS ( SELECT   parentname = COALESCE(OBJECT_NAME(obj.parent_object_id),
                                             obj.name) ,
                       name = obj.name ,
                       schemaname = SCHEMA_NAME(obj.[schema_id]) ,
                       [type_desc] = obj.[type_desc] ,
                       major_id = obj.[object_id] ,
                       minor_id = 0,
class_desc = N'OBJECT_OR_COLUMN'
              FROM     sys.objects AS obj
              WHERE    obj.is_ms_shipped = 0
              UNION ALL
              SELECT   parentname = OBJECT_NAME(c.[object_id]) ,
                       name = c.name ,
                       schemaname = OBJECT_SCHEMA_NAME(c.[object_id]) ,
                       [type_desc] = N'COLUMN' ,
                       major_id = c.[object_id] ,
                       minor_id = c.column_id,
class_desc = N'OBJECT_OR_COLUMN'
              FROM     sys.columns AS c
              WHERE    OBJECTPROPERTYEX(c.[object_id], 'IsMSShipped') = 0
                       AND OBJECTPROPERTYEX(c.[object_id], 'IsUserTable') = 1 --only document table columns, not views/functions. Remove predicate if required.
              UNION ALL
              SELECT   parentname = OBJECT_NAME(i.[object_id]) ,
                       name = i.name ,
                       schemaname = OBJECT_SCHEMA_NAME(i.[object_id]) ,
                       [type_desc] = N'INDEX' ,
                       major_id = i.[object_id] ,
                       minor_id = i.index_id,
class_desc = N'INDEX'
              FROM     sys.indexes AS i
              WHERE    OBJECTPROPERTYEX(i.[object_id], 'IsMSShipped') = 0
                       AND i.is_primary_key = 0 --the constraint is already documented, don't document the index too. Remove predicate if required.
                       AND i.is_unique_constraint = 0 --the constraint is already documented, don't document the index too. Remove predicate if required.
AND i.[type_desc] <> N'HEAP' --the table is already documented, don't document the heap index row too.
            ),
/*Join objects on which to create extended properties to the parameters, performing string replacement where necessary.*/
       Parameter_Value
         AS ( SELECT   o.major_id ,
                       o.minor_id ,
o.class_desc,
                       p.parameter ,
[name] = N'MS_Description',
                       val = CASE p.val
                               WHEN N'_replace_schemaname' THEN o.schemaname
                               WHEN N'_replace_parentname' THEN o.parentname
                               WHEN N'_replace_name' THEN o.name
                               WHEN N'_replace_value'
                               THEN SYSTEM_USER + N' '
                                    + CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
                                    + N': ' + p.val
                               ELSE p.val
                             END
              FROM     @Parameter AS p
                       INNER JOIN Obj AS o ON o.[type_desc] = p.[type_desc] COLLATE DATABASE_DEFAULT
            )
/*pivot the result set so we have one correctly formatted extended property create statement per object.*/
   SELECT  Add_Extended_Property = N'EXECUTE sp_addextendedproperty'
           + N' @name = ''' + [name] + N''', @value = ' + [value]
           + N', @level0type = ' + [level0type] + N', @level0name = '
           + [level0name] + N', @level1type = ' + [level1type]
           + N', @level1name = ' + [level1name] + N', @level2type = '
           + [level2type] + N', @level2name = ' + [level2name] + N';'
   FROM    ( SELECT    pv.major_id ,
                       pv.minor_id ,
pv.class_desc,
pv.[name],
                       pv.parameter ,
                       val = CASE pv.val
                               WHEN N'NULL' THEN pv.val
                               ELSE '''' + pv.val + ''''
                             END
             FROM      Parameter_Value AS pv
             WHERE     NOT EXISTS ( SELECT *
                                    FROM   sys.extended_properties AS ep
                                    WHERE  ep.major_id = pv.major_id
                                           AND ep.minor_id = pv.minor_id
AND ep.class_desc = pv.class_desc
AND ep.[name] = pv.[name] )
           ) AS SourceTable PIVOT
( MIN(val) FOR parameter IN (  [value], [level0type], [level0name],
                             [level1type], [level1name], [level2type],
                             [level2name] ) ) AS PivotTable
   ORDER BY Add_Extended_Property;

Instruction

First, run the script above against your database. This results in an output such as this:

Next, replace _replace_value with your required information. Then run the script to create your extended properties.

Method to add extended properties to an existing system

When inheriting a database with no documentation, it can be overwhelming to attempt to document thousands of objects. I generally add extended properties via three methods.

If you get questioned about some part of the database, document what you know via an extended property rather than simply replying via email for example. It is likely others will have the same question in the future and you won’t have to repeat yourself.

For new objects, use snippets or templates. Write the extended property create statement in there and you will always remember to add.

Lastly, pick off existing objects at a few per day or when you have some spare time. Run the script above to generate the statements to create extended properties for currently undocumented objects and create.

Conclusion

Some say DBAs are lazy, I would prefer to say efficient. Automate everything that can be automated and focus your skills and productivity at more important and interesting areas.

Don’t answer the same questions from the development team over and over about the database design, clearly and concisely document it once within the database where everyone can see and consume it. Don't repeat yourself (DRY).

When the regulatory requirement comes from either within your company or an external body, use a commercial product to auto-generate a beautiful professional looking document that will include your extended properties to describe the function of everything within your database.

And finally, when you have to work on that long forgotten database solution you once delivered because performance is now painfully slow (definitely not your fault, data volumes are now way more than you were told they would be), you can remember why you designed the tables in that way.

 
Total article views: 5343 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Modifying and deleting extended properties

Continuing the short series on extended properties, this article explains how to turbocharge the cre...

FORUM
ARTICLE

Extracting Extended Properties

In this second article of a short series we look at using the Extended Properties which you have add...

ARTICLE

Extended Properties Introduction

Part one of a four part series intent on demystifying and making more accessible SQL Server extended...

ARTICLE

Industrial-strength database documentation using Extended Properties

To finish this short series on extended properties a look at documenting sets of database objects

Tags
 
Contribute