To use extended properties for documentation is a striking thought, 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.
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
CodePudding user response：
This tool ( which I had to write myself ) consists of
- Table, which contains all extended properties ( and some additional information )
- Procedure, to fill this table, which runs daily or whenever needed
- Procedure, to set easily a huge bunch of Extended Properties in one step
- Optional: a SSRS report to search and browse the documentation-Table
These are the moving parts:
- 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
)
- 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
- 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
- SSRS-Report to search or browse the documentation-Table
this report could look like this :
CodePudding user response：
Dear https://stackoverflow.com/users/2370483/machavity
there is a limit of 30.000 letters for an answer, the code of procedure [dbo].[usp_make_extended_properties_statements] didn't fit anymore. Please undelete my post
Sincerely cjonas123