Auto_Generate_Database_Documentation.sql

/*
.EXAMPLE
    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.

.NOTE
    Version: 1.1
    Modified: 2018-03-30 23:40:00 UTC+3 Konstantin Taranov
    Link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Auto_Generate_Database_Documentation.sql
    Created: 2015-11-25
    Author: (C) Andy Jones mailto:andrew@aejsoftware.co.uk
*/
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;