Is there an easy and convenient way to use SQL Server extended properties for automatic documentation?

Question

To use extended properties for documentation is a striking thought, or to have a easy way to tag your tables, but to set a snippet of documentation you have to execute something like:

EXECUTE sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'custom description text', 
    @level0type = N'SCHEMA', @level0name = N'SchemaName', 
    @level1type = N'TABLE', @level1name = N'TableName';

or to execute several mouse-clicks until you can enter your description.

If you want to browse your documentation you can use some selects like

select
    SysTbls.name as [Table Name],
    ExtProp.name as [Extended Property Name],
    ExtProp.value as [Extended Property],
    SysObj.create_date as [Table Create Date],
    SysObj.modify_date as [Table Modify Date],
    ExtProp.minor_id 
from 
    sys.tables SysTbls
left join 
    sys.extended_properties ExtProp on ExtProp.major_id = SysTbls.object_id
left join 
    sys.objects SysObj on SysTbls.object_id = SysObj.object_id
where 
    ExtProp.class = 1 --Object or column
    and SysTbls.name is not null;

Or you can use tools like Red-Gate Documentation, but this tool seemed to be limited to the extended property "MS_Description".

So when you have to document several databases with several dozen tables with hundred of columns and several dozen of procedures/views etc. and if you like to look up some extended properties like "Ticket-Number" or "needs dataprotection", you need a tool to speed up the work. To repeat the question in the title: Is there an easy and convenient way to use SQL Server extended properties for automatic documentation? No, unless you write it yourself

---

Below you find the second part of my answer. I had to split the answer, because one post is limited to 30.000 characters. Seems, there is a rule, that the answer has to be in one post. So someone put the second part of the answer in the question. So, please, if you interested, read the answer first, then return here for the second part. Sorry for the inconvenience

---

here is the procedure-code from Step 3

create procedure [dbo].[usp_make_extended_properties_statements] @source_table nvarchar(100) , @refresh_documentation_table bit = 1
as
begin
set nocount on
--
-- =============================================
-- Name       : usp_make_extended_properties_statements
-- Description: 
-- it compares dbo.documentation with the values from the source_table and creates insert/update/delete-Statements for extended properties
-- Step 1) create the source_table : e.g.: select * into table_1 from dbo.documentation
-- you can restrict on lines from interest e.g. select * into only_clients_tables from dbo.documentation where Object_Name like '%client%'

-- Step 2)  edit your source_table
--          e.g. update only_clients_tables 
--               set Extended_Property = 'dataprotect' and Extended_Property_Value = 'true'
--               where Column_or_Index_Name in ('name', 'surname' ,'street', 'zip' , 'town')
--          or use a tool, e.g. Aquadata-DataEdit, where you can handle it like a excel-Sheet
--          or any other tool you favorize
-- it is possible to  insert, update und delete extended properties with the source-Table
-- to delete extended properties empty the columns Extended_Property and Extended_Property_Value (set to value <null> )

-- Step 3) exec usp_make_extended_properties_statements 'yourDatabase.yourSchema.yourSourceTableName'
-- this will result in bunch of statements, copy them to a query-window and execute them

-- Step 4) exec dbo.usp_fill_doc_table  
-- =============================================

declare @sql nvarchar(4000)
        ,@dbname nvarchar(100)
        ,@schemaname nvarchar(100)
        ,@tablename nvarchar(255)


        SET @dbname     = PARSENAME(@source_table, 3);
        SET @schemaname = PARSENAME(@source_table, 2);
        SET @tablename  = PARSENAME(@source_table, 1);

        select @dbname ,@schemaname,@tablename


        if ( @tablename is null or @schemaname is null or @dbname is null )
        begin
            print 'table '+@source_table+' doesnt exist or Parameter not given in format DBname.Schemaname.Tablenname ' + CHAR(13) + 'which was created by: select * into source_table from dbo.documentation' + CHAR(13) + ' for editing (insert/update/delete)' + CHAR(13) + ' after editing:  exec usp_make_extended_properties_statements ''source_table''
            '
            return 0
        end

        set @sql = '
        select count(1) from '+@dbname+'.sys.columns c join '+@dbname+'.sys.tables t on t.object_id = c.object_id where t.name = '''+@tablename+'''
        and c.name in (N''Object_Type'', N''Database_Name'', N''Schema_Name'', N''Object_Name'', N''Column_or_Index_Name'', N''Extended_Property'', N''Extended_Property_Value'')
        '

        create table #soll (
        Object_Type varchar(60) NULL,
        Database_Name varchar(50) NULL,
        Schema_Name varchar(30) NOT NULL,
        Object_Name varchar(128) NOT NULL,
        Column_or_Index_Name varchar(100) NULL,
        Extended_Property varchar(100) NULL,
        Extended_Property_Value varchar(5000) NULL
         )
        --declare @sql nvarchar(4000), @source_table nvarchar(100) = 'worker.cjonas.ep'
        set @sql = '
        select Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value
        from '+@source_table+' where Object_type is not null'

        begin try
            insert into #soll
                (
                    Object_Type,
                    Database_Name,
                    Schema_Name,
                    Object_Name,
                    Column_or_Index_Name,
                    Extended_Property,
                    Extended_Property_Value
                )
            EXEC SP_EXECUTESQL @sql
        end try
        begin catch
                select @source_table+' must have columns Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value '+error_message()

        end catch

        --replace empty String with <null>
        update #soll set Column_or_Index_Name = null where Column_or_Index_Name = ''


        -- retrieve actual state:
        if ( @refresh_documentation_table  = 1)  exec dbo.usp_fill_doc_table;

        select distinct d.Object_Type,
                d.Database_Name,
                d.Schema_Name,
                d.Object_Name,
                d.Column_or_Index_Name,
                d.Extended_Property,
                d.Extended_Property_Value 
        into #ist
        from dbo.documentation d join #soll s on d.Database_Name = s.Database_Name and d.Schema_name = s.Schema_name and d.Object_name = s.Object_name
        where d.Object_Type is not null

        
        --------------------------------------
        -- create insert or update-Statements 


     declare 
     @Object_Type sysname, @Database_Name sysname, @Schema_Name sysname, @Object_Name sysname,@level1type sysname,
     @Extended_Property nvarchar(1000),@Extended_Property_Value nvarchar(1000), 
     @Column_or_Index_Name sysname = null

      --select * from #ist; select * from #soll 
     -- 
      ----------------------------------------------------------------
     ---- add or  update extended_properties
     ----------------------------------------------------------------
          select Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value 
          from #soll where Extended_Property is not null
          except
          select Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value
          from #ist  where Extended_Property is not null;


     declare db_cursor cursor local fast_forward for
          select Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value 
          from #soll where Extended_Property is not null and Extended_Property <> 'no extended property'
          except
          select Object_Type, Database_Name, Schema_Name, Object_Name, Column_or_Index_Name, Extended_Property, Extended_Property_Value
          from #ist  where Extended_Property is not null and Extended_Property <> 'no extended property';
     open db_cursor;
     fetch next from db_cursor into @Object_Type , @Database_Name , @Schema_Name , @Object_Name, @Column_or_Index_Name , @Extended_Property ,@Extended_Property_Value 

     
    while @@FETCH_STATUS = 0  
     begin

         IF DB_ID(@Database_Name) IS NULL
        BEGIN
            RAISERROR ('Unknown Database Name %s', 11, 1, @Database_Name);
            return -1;
        end

        

    -- Trim @Object_Type only allowed values
    -- for @level1type : Aggregate, Default, Function, Logical File Name, PROCEDURE, Queue, Rule, Sequence, Synonym, Table, TABLE_TYPE, Type, View, XML Schema Collection and NULL
    -- for @level2type : COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER und NULL
     
     select @Object_Type = 
        case 
            when @Object_Type like '%procedure'         then 'PROCEDURE'
            when @Object_Type like '%index'             then 'INDEX'
            when @Object_Type like '%function'          then 'FUNCTION'
            when @Object_Type in   ('foreign_key','check','primary_key','unique') then 'CONSTRAINT'
            when @Object_Type =    'sequence_object'    then 'SEQUENCE'
            else @Object_Type
        end

    
      ---------  
      IF @Column_or_Index_Name IS null
      begin
        SET @sql = CONCAT('use ', @Database_Name, CHAR(13) + CHAR(10)
                        , 'go', CHAR(13) + CHAR(10)
                        , 'if not exists (select 1 from sys.extended_properties AS ep WHERE ep.major_id = OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND ep.minor_id = 0 AND ep.name = ''', @Extended_Property,''')'
                        , char(13) +  char(10) 
                        , 'begin'
                        , char(13) +  char(10) 
                        , 'exec ', @Database_Name,'.sys.sp_addextendedproperty    @name = ''', @Extended_Property,''', @value =''', @Extended_Property_value,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''', @Object_Type,''',  @level1name = ''', @Object_Name,''''
                        , char(13) +  char(10)
                        , 'end'
                        , char(13) +  char(10) 
                        , 'else begin'
                        , char(13) +  char(10)
                        , 'exec ', @Database_Name,'.sys.sp_updateextendedproperty @name = ''', @Extended_Property,''', @value = ''', @Extended_Property_value,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''', @Object_Type,''',  @level1name = ''', @Object_Name,''''
                        , char(13) +  char(10)
                        ,'end;'
                        , char(13) +  char(10)
                         );
    end  -- if
    else    
    begin

    --- table or view:
    set @level1type = (
    select distinct Object_Type as level1type 
    from dbo.documentation d 
    where Object_Type in ( 'table','view') and Object_Name= @Object_Name and Schema_Name = @Schema_Name and Database_Name = @Database_Name
    )

    select  @level1type as  level1type
    select *
    from sys.objects o 
    join sys.schemas s on s.schema_id = o.schema_id
    where o.name = @Object_Name and s.name = @Schema_Name
    --- Spalte oder Index:
        set @sql = concat('use ', @Database_Name, char(13) + char(10)
                        , 'go'
                        , char(13) + char(10)
                        , 'if not exists '
                        , char(13) + char(10)
                        , '(select 1 from SYS.columns AS c INNER JOIN sys.extended_properties as ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id and ep.name = ''',@Extended_Property,'''',char(13) + char(10) 
                        , 'where c.object_id =  OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND c.name = ''',@Column_or_Index_Name, ''')'
                        , char(13) + char(10) 
                        , 'and not exists ',char(13) + char(10) 
                        , '(SELECT 1 FROM sys.extended_properties ep join sys.indexes i ON ep.major_id =  i.object_id AND ep.name = ''',@Extended_Property,'''',char(13) + char(10) 
                        , 'where ep.major_id =  OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND i.name = ''',@Column_or_Index_Name, ''')'
                        , char(13) + char(10)
                        , 'begin '
                        , char(13) + char(10)
                        , 'exec ', @Database_Name,'.sys.sp_addextendedproperty    @name = ''', @Extended_Property,''', @value = ''', @Extended_Property_value,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''',@level1type,''',  @level1name = ''', @Object_Name,''', @level2type = ''', @Object_Type,''',  @level2name = ''',@Column_or_Index_Name,''''
                        , char(13) + char(10)
                        , 'end'
                        , char(13) + char(10)
                        , 'else begin '
                        , char(13) + char(10)
                        , 'exec ', @Database_Name,'.sys.sp_updateextendedproperty @name = ''', @Extended_Property,''', @value = ''', @Extended_Property_value,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''',@level1type,''',  @level1name = ''', @Object_Name,''', @level2type = ''', @Object_Type,''',  @level2name = ''', @Column_or_Index_Name,''''
                        , char(13) + char(10)
                        , 'end ;'
                        , char(13) + char(10)
                         )
        ;
      end  ---ifelse
        
            print '-- Insert or update extended properties:'
            print @sql
       --EXEC SP_EXECUTESQL @sql

         fetch next from db_cursor into @Object_Type , @Database_Name , @Schema_Name , @Object_Name, @Column_or_Index_Name , @Extended_Property ,@Extended_Property_Value ;
     end;

     close db_cursor;
     deallocate db_cursor;


     -- --------------------
   select
        i.Object_Type,
        i.Database_Name,
        i.Schema_Name,
        i.Object_Name,
        i.Column_or_Index_Name,
        i.Extended_Property
      from #soll     s
     right join #ist i
        on i.Object_Type                       = s.Object_Type
       and i.Database_Name                     = s.Database_Name
       and i.Schema_Name                       = s.Schema_Name
       and i.Object_Name                       = s.Object_Name
       and isnull(i.Extended_Property, 'null')   = isnull(s.Extended_Property, 'null')
       and isnull(i.Column_or_Index_Name,'null') = isnull(s.Column_or_Index_Name,'null')
     where s.extended_property is null
       and i.Object_type is not null and i.extended_property <> 'no extended property'


     ----------------------------------------------------------------
     ---- deleted extended_properties
     ----------------------------------------------------------------
     declare db_cursor2 cursor local fast_forward for
        select
        i.Object_Type,
        i.Database_Name,
        i.Schema_Name,
        i.Object_Name,
        i.Column_or_Index_Name,
        i.Extended_Property
      from #soll     s
     right join #ist i
        on i.Object_Type                       = s.Object_Type
       and i.Database_Name                     = s.Database_Name
       and i.Schema_Name                       = s.Schema_Name
       and i.Object_Name                       = s.Object_Name
       and isnull(i.Extended_Property, 'null')   = isnull(s.Extended_Property, 'null')
       and isnull(i.Column_or_Index_Name,'null') = isnull(s.Column_or_Index_Name,'null')
     where s.extended_property is null
       and i.Object_type is not null and i.extended_property <> 'no extended property'

     open db_cursor2;
     fetch next from db_cursor2 into @Object_Type , @Database_Name , @Schema_Name , @Object_Name, @Column_or_Index_Name , @Extended_Property

     while @@FETCH_STATUS = 0  
     begin

         IF DB_ID(@Database_Name) IS NULL
        BEGIN
            RAISERROR ('Unknow Databasename %s', 11, 1, @Database_Name);
            return -1;
        end  
    -- Trim @Object_Type only allowed values
    -- for @level1type : Aggregate, Default, Function, Logical File Name, PROCEDURE, Queue, Rule, Sequence, Synonym, Table, TABLE_TYPE, Type, View, XML Schema Collection und NULL
    -- for @level2type : COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER und NULL
     
     select @Object_Type = 
        case 
            when @Object_Type like '%procedure'         then 'PROCEDURE'
            when @Object_Type like '%index'             then 'INDEX'
            when @Object_Type like '%function'          then 'FUNCTION'
            when @Object_Type in   ('foreign_key','check','primary_key','unique') then 'CONSTRAINT'
            when @Object_Type =    'sequence_object'    then 'SEQUENCE'
            else @Object_Type
        end


        if @Column_or_Index_Name is null
        begin
        set @sql =  concat('use ', @Database_Name
                        ,  char(13) +  char(10)
                        , 'go'
                        ,  char(13) +  char(10)
                        , 'if exists (select 1 from sys.extended_properties AS ep '
                        , 'WHERE ep.major_id =  OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND ep.minor_id = 0 AND ep.name = ''',@Extended_Property,''')'
                        ,  char(13) + char(10) 
                        , 'EXEC ', @Database_Name,'.sys.sp_dropextendedproperty  @name = ''', @Extended_Property,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''', @Object_Type,''',  @level1name = ''', @Object_Name,''''
                        ,';'
                        ,  char(13) + char(10)
                         )
           
        end
        else
        begin

        --- table or view:
        set @level1type = (
        select distinct Object_Type as level1type 
        from dbo.documentation d 
        where Object_Type in ( 'table','view') and Object_Name= @Object_Name and Schema_Name = @Schema_Name and Database_Name = @Database_Name
        )
        --- column or Index:
        set @sql =  concat('USE ', @Database_Name, char(13) + char(10)
                        , 'go', char(13) + char(10)
                        , 'IF EXISTS' 
                        , char(13) + char(10)
                        ,'(SELECT 1 FROM SYS.columns AS c INNER JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = ''',@Extended_Property,''''
                        ,char(13) + char(10) 
                        , 'WHERE c.object_id =  OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND c.name = ''',@Column_or_Index_Name, ''')'
                        ,char(13) + char(10) 
                        , 'or exists '
                        ,char(13) + char(10) 
                        ,'(SELECT 1 FROM sys.extended_properties ep join sys.indexes i ON ep.major_id =  i.object_id AND ep.name = ''',@Extended_Property,''''
                        ,char(13) + char(10) 
                        , 'WHERE ep.major_id =  OBJECT_ID(''',@Schema_Name ,'.', @Object_Name,''') AND i.name = ''',@Column_or_Index_Name, ''')'
                        , char(13) + char(10)
                        , 'EXEC ', @Database_Name,'.sys.sp_dropextendedproperty  @name = ''', @Extended_Property,''', @level0type = N''SCHEMA'', @level0name = ''', @Schema_Name,''', @level1type = ''',@level1type,''',  @level1name = ''', @Object_Name,''', @level2type = ''', @Object_Type,''',  @level2name = ''', @Column_or_Index_Name,'''' 
                        ,';'
                        , char(13) + char(10)
                         )
           
        end
        print '-- Delete extended properties:'
        print @sql
        --EXEC SP_EXECUTESQL @sql
        fetch next from db_cursor2 into @Object_Type , @Database_Name , @Schema_Name , @Object_Name, @Column_or_Index_Name , @Extended_Property ;
     end;
       
     close db_cursor2;
     deallocate db_cursor2;

     --- actualize Documentation-table:
     --- only when you have uncommented every: "--EXEC SP_EXECUTESQL @sql"
     --- then uncomment following line, too:
     --  if ( @refresh_documentation_table  = 1)  exec dbo.usp_fill_doc_table;

end

Answer 1

This tool ( which I had to write myself ) consists of

1. Table, which contains all extended properties ( and some additional information )
2. Procedure, to fill this table, which runs daily or whenever needed
3. Procedure, to set easily a huge bunch of Extended Properties in one step
4. Optional: a SSRS report to search and browse the documentation-Table

These are the moving parts:

1. Table to hold the documentation

create table [dbo].[documentation]
(
    [Object_Type] [varchar](60) null,
    [Database_Name] [varchar](50) null,
    [Schema_Name] [varchar](30) not null,
    [Object_Name] [varchar](128) not null,
    [Column_or_Index_Name] [varchar](256) null,
    [Extended_Property] [varchar](100) null,
    [Extended_Property_Value] [varchar](5000) null,
    [Data_Type] [sysname] null,
    [Column_Length] [varchar](22) null,
    [Column_is_Nullable] [varchar](3) null,
    [Create_Date] [datetime2](0) null,
    [Modify_Date] [datetime2](0) null,
    [Column_Number] [smallint] null
)

2. Procedure to fill the documentation table:

CREATE PROCEDURE [dbo].[usp_fill_doc_table] 
AS
BEGIN
    SET NOCOUNT ON;

-- =============================================
-- Name       : usp_fill_doc_table
-- Description: scans all databases ( except master,tempdb etc. ) for extended properties and fills table dbo.documentation
--              all objects, whether they do have a extended property or not are listed              
-- =============================================

declare @sql1 nvarchar(2000)
declare @sql2 nvarchar(2000)
declare @sql3 nvarchar(2000)

truncate table dbo.documentation

-- objects with extended_properties

        set @sql1  ='
use ?
if (  db_name(db_id()) not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')  )
begin
insert into dbo.documentation            
select
case when SC.name is null then lower(replace(replace(replace(SO.type_desc,''USER_'',''''),''SQL_'',''''),''_CONSTRAINT'','''')) else ''column'' end  o1,
db_name() db,
SS.name sn,
SO.name so,
SC.name sc,
EP.name epn,
try_cast(EP.value as varchar(5000)) ep,
ST.name stn,
case when ST.name in (''nvarchar'', ''nchar'')
then convert(varchar(10), ( SC.max_length / 2 ))
when ST.name in (''char'', ''varchar'')
then convert(varchar(10), SC.max_length)
else null
end epv,
case when SC.is_nullable = 0 then ''No'' when SC.is_nullable = 1 then ''Yes'' else null end n,
case when SC.name is null then SO.create_date else null end c,
case when SC.name is null then SO.modify_date else null end m,
isnull(SC.column_id,0) ci
from sys.objects SO
join sys.schemas SS
on SS.schema_id = SO.schema_id
left join sys.extended_properties EP
on EP.major_id = SO.object_id
left join sys.columns SC
on EP.major_id = SC.object_id
and EP.minor_id = SC.column_id
left join sys.types ST
on SC.user_type_id = ST.user_type_id and SC.system_type_id = ST.system_type_id
where SO.is_ms_shipped = 0 and ep.class_desc = ''OBJECT_OR_COLUMN''
  union all
select distinct
lower(EP.class_desc) o1,
db_name() db,
SS.name sn,
SO.name so,
i.name sc,
EP.name epn,
try_cast(EP.value as varchar(5000)) ep,
null stn,
null epv,
null n,
null c,
null m,
0 ci
from sys.objects SO
join sys.schemas SS
on SS.schema_id = SO.schema_id
left join sys.extended_properties EP
on EP.major_id = SO.object_id
left join sys.indexes i on i.object_id = SO.object_id and i.index_id = ep.minor_id
where SO.is_ms_shipped = 0 and ep.class_desc <> ''OBJECT_OR_COLUMN'' 
end
'
        ;

        -- objects without extended_properties, but with columns/index etc
       
        set @sql2  ='
use ?
if (  db_name(db_id()) not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')  )
begin   
insert into dbo.documentation
select
    case when SC.name is null then lower(replace(replace(replace(SO.type_desc,''USER_'',''''),''SQL_'',''''),''_CONSTRAINT'','''')) else ''column'' end  o1,
    db_name() db,
    SS.name sn,
    SO.name so,
    SC.name sc,
    ''no extended property'' epn,
    try_cast(EP.value as varchar(5000)) ep,
    ST.name stn,
    case when ST.name in (''nvarchar'', ''nchar'')
        then convert(varchar(10), ( SC.max_length / 2 ))
        when ST.name in (''char'', ''varchar'')
        then convert(varchar(10), SC.max_length)
        else null
    end epv,
    case when SC.is_nullable = 0 then ''No'' when SC.is_nullable = 1 then ''Yes'' else null end n,
    case when SC.name is null then SO.create_date else null end c,
    case when SC.name is null then SO.modify_date else null end m,
    isnull(SC.column_id,0) ci
from sys.objects                  SO
join sys.schemas                  SS
    on SS.schema_id = SO.schema_id
join sys.columns             SC
on SO.object_id     = SC.object_id
left join sys.extended_properties EP
    on EP.major_id     = SO.object_id
    and EP.minor_id     = SC.column_id
left join sys.types               ST
    on SC.user_type_id = ST.user_type_id and SC.system_type_id = ST.system_type_id
    where SO.is_ms_shipped = 0 and EP.minor_id is null and SO.type <>''IF''
end
'
;

        -- Objects without extended_properties and without Columns
        set @sql3  ='
use ?
if (  db_name(db_id()) not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')  )
begin   
insert into dbo.documentation
select
    case when i.name is null then lower(replace(replace(replace(SO.type_desc,''USER_'',''''),''SQL_'',''''),''_CONSTRAINT'','''')) 
    else case when i.is_unique =1 then ''unique '' else '''' end  
    + lower(i.type_desc) + '' index'' end o1,
    db_name() db,
    SS.name sn,
    SO.name so,
    i.name sc,
    ''no extended property'' epn,
    null as epv,
    null as dt,
    null as cl,
    null as cin,
    SO.create_date,
    SO.modify_date,
    0
from sys.objects SO
join sys.schemas SS
    on SS.schema_id = SO.schema_id
left join sys.indexes i on SO.object_id = i.object_id
 where SO.is_ms_shipped = 0
insert into dbo.documentation
select
    lower(replace(replace(replace(SO.type_desc,''USER_'',''''),''SQL_'',''''),''_CONSTRAINT'','''')) o1,
    db_name() db,
    SS.name sn,
    SO.name so,
    null,
    ''no extended property'' epn,
    null as epv,
    null as dt,
    null as cl,
    null as cin,
    SO.create_date,
    SO.modify_date,
    0
from sys.objects SO
join sys.schemas SS
    on SS.schema_id = SO.schema_id
 where SO.is_ms_shipped = 0
end
'
;

    exec sp_msforeachdb @command1 = @sql1,@command2 = @sql2 ,@command3 = @sql3 

      --- some special objects like _ST_2610A626_00000003_00005585 are not interesting:
    delete from dbo.documentation where Object_type is null


        --- beautify the result:

        -- remove double

        ;with dubletten as (
                select count(1) as z,Object_Type,Database_Name,Schema_Name,Object_Name,Column_or_Index_Name,Extended_Property
                from dbo.documentation  
                group by Object_Type,Database_Name,Schema_Name,Object_Name,Column_or_Index_Name,Extended_Property
                having count(1) >1)
        select distinct a.* into #dubletten
        from dbo.documentation a
          join dubletten                doppelte
            on a.Object_Name          = doppelte.Object_Name
           and a.Schema_Name          = doppelte.Schema_Name
           and a.Database_Name        = doppelte.Database_Name
           and a.Object_Type          = doppelte.Object_Type
           and a.Extended_Property    = doppelte.Extended_Property


        delete from dbo.documentation
        from dbo.documentation a
          join #dubletten                doppelte
            on a.Object_Name          = doppelte.Object_Name
           and a.Schema_Name          = doppelte.Schema_Name
           and a.Database_Name        = doppelte.Database_Name
           and a.Object_Type          = doppelte.Object_Type
           and a.Extended_Property    = doppelte.Extended_Property

        insert into dbo.documentation
        select * from #dubletten d


        --- one object shouldn't be listed twice with a) 'no extended property' and with b) 'MS_Description'
        --- a) double-listed objects with a extended property set

    ;with alle_mehrfachen_objekte as (
        select count(1) as z,Object_Type,Database_Name,Schema_Name,Object_Name 
        from dbo.documentation where Column_or_Index_Name is null  
        group by Object_Type,Database_Name,Schema_Name,Object_Name
        having count(1) >1)
    delete from dbo.documentation
      from dbo.documentation a
      join alle_mehrfachen_objekte                doppelte
        on a.Object_Name          = doppelte.Object_Name
       and a.Schema_Name          = doppelte.Schema_Name
       and a.Database_Name        = doppelte.Database_Name
       and a.Object_Type          = doppelte.Object_Type
     where a.Column_or_Index_Name is null
       and a.Extended_Property = 'no extended property'
       and ( a.Extended_Property_Value is null or a.Extended_Property_Value = '');

        --- b) double-listed objects which are columns, with a extended property set
    ;with alle_mehrfachen_objekte as (
        select count(1) as z,Database_Name,Schema_Name,Object_Name,Column_or_Index_Name 
        from dbo.documentation 
        where Column_or_Index_Name is not null and Object_Type = 'column' 
        group by Database_Name,Schema_Name,Object_Name,Column_or_Index_Name
        having count(1) >1)
    delete from dbo.documentation  
      from dbo.documentation a
      join alle_mehrfachen_objekte                doppelte
        on a.Object_Name          = doppelte.Object_Name
       and a.Schema_Name          = doppelte.Schema_Name
       and a.Database_Name        = doppelte.Database_Name
       and a.Column_or_Index_Name          = doppelte.Column_or_Index_Name
     where a.Column_or_Index_Name is not null
        and a.Object_Type = 'column'
       and a.Extended_Property = 'no extended property'
       and ( a.Extended_Property_Value is null or a.Extended_Property_Value = '');

        --- b) double-listed objects which are not columns ( but e.g. index ), with a extended property set
    ;with alle_mehrfachen_objekte as (
        select count(1) as z,Database_Name,Schema_Name,Object_Name,Column_or_Index_Name 
        from dbo.documentation 
        where Column_or_Index_Name is not null and Object_Type <> 'column' 
        group by Database_Name,Schema_Name,Object_Name,Column_or_Index_Name
        having count(1) >1)
    delete from dbo.documentation  
      from dbo.documentation a
      join alle_mehrfachen_objekte                doppelte
        on a.Object_Name          = doppelte.Object_Name
       and a.Schema_Name          = doppelte.Schema_Name
       and a.Database_Name        = doppelte.Database_Name
       and a.Column_or_Index_Name          = doppelte.Column_or_Index_Name
     where a.Column_or_Index_Name is not null
        and a.Object_Type <> 'column'
       and a.Extended_Property = 'no extended property'
       and ( a.Extended_Property_Value is null or a.Extended_Property_Value = '');
end

3. Procedure to create all the extended-properties-Statements based on a table as input

    1. create the source_table : e.g.: select * into table_1 from dbo.documentation
     - you can restrict on lines from interest e.g. select * into only_clients_tables from dbo.documentation where Object_Name like '%client%'
    2. edit your source_table
     - e.g. update only_clients_tables 
              set Extended_Property = 'dataprotect' and Extended_Property_Value = 'true'
             where Column_or_Index_Name in ('name', 'surname' ,'street', 'zip' , 'town')
        - or use a tool, e.g. Aquadata-DataEdit, where you can handle it like a excel-Sheet
        - or any other tool you favorize
        - it is possible to  insert, update und delete extended properties with the source-Table
        - to delete extended properties empty the columns Extended_Property and Extended_Property_Value (set to value <null> )

    3. exec usp_make_extended_properties_statements 'yourDatabase.yourSchema.yourSourceTableName'
        - this will result in bunch of statements, copy them to a query-window and execute them

    4. exec dbo.usp_fill_doc_table 

4. SSRS-Report to search or browse the documentation-Table

this report could look like this :