source-controllable self-enforcing extended attribute list for sql server

extended_attributes.sql

-- Permanent copy of schema extended properties
-- In a format suitable for easy source control and hand-editing.


-- All the properties are prefixed and only those will be added/updated/deleted by this script

-- At the bottom you'll find a commented-out `select` for generating the
--   insert block from an existing schema's extended properties.

-- This script will add/update/remove properties from the schema it's
--   run against to bring them into line with the below list.

-- https://gist.github.com/timabell/6fbd85431925b5724d2f

set nocount on;
--set xact_abort on;

--begin tran
declare @prefix varchar(200) = 'NamespaceHere_'
declare @properties table (id int primary key identity(1,1), [table] sysname, [column] sysname null, name sysname, [value] sql_variant);

insert into @properties ([table], [column], name, [value]) values
('sometable', 'somefield', 'attribname1', N'teh value'),
('sometable', 'someotherfield', 'attribname2', N'yeah really'),
('someothertable', 'yetanotherfield', 'attribname3', N'oh no you don''t')
;

declare @action_delete varchar(10) = 'delete';
declare @action_update varchar(10) = 'update';
declare @action_add varchar(10) = 'add';

-- set up list of properties to check
declare mergeList cursor for
    select
        isnull(prop.[table], existing.[table]) [table],
        isnull(prop.[column], existing.[column]) [column],
        isnull(prop.name, existing.name) name,
        prop.value newvalue,
        case
            when prop.id is null then @action_delete
            when existing.[table] is null then @action_add
            else @action_update
        end as action
    from
        @properties prop
        full outer join
        (
            select
                tbl.name [table],
                col.name [column],
                substring(ep.name,7,200) name,
                ep.value [value]
            from sys.extended_properties ep
                inner join sys.objects tbl on tbl.object_id = ep.major_id
                    and tbl.name not like '\_\_%' escape '\' -- ignore the ready-roll object(s)
                    and tbl.name not like 'ast%' -- ignore the generated enum tables, they already have their own values
                left outer join sys.columns col on col.object_id = ep.major_id and col.column_id = ep.minor_id
            where ep.name like @prefix + '%'
        ) existing
            on existing.[table] = prop.[table]
                and existing.name = prop.name
                and (existing.[column] = prop.[column] or (existing.[column] is null and prop.[column] is null))
    where prop.value <> existing.value or prop.value is null or existing.value is null
;

open mergeList;

declare @table sysname;
declare @column sysname;
declare @fullname sysname;
declare @name sysname;
declare @newvalue sql_variant;
declare @action varchar(10);

fetch next from mergeList into @table, @column, @name, @newvalue, @action;
while @@FETCH_STATUS = 0
begin
    --print concat(@table, '.', @column, ' ', @prefix, @name, ' - ', @action);
    set @fullname = @prefix + @name
    if @action = @action_add
    begin
        if @column is null
        begin
            print 'adding property ' + @fullname + ' for ' + @table;
            exec sys.sp_addextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
                @level1name=@table, @value=@newvalue
        end
        else
        begin
            print 'adding property ' + @fullname + ' for ' + @table + '.' + @column;
            exec sys.sp_addextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level2type=N'COLUMN',
                @level1name=@table, @level2name=@column, @value=@newvalue
        end
    end
    else if @action = @action_update
    begin
        if @column is null
        begin
            print 'updating property ' + @fullname + ' for ' + @table;
            exec sys.sp_updateextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
                @level1name=@table, @value=@newvalue
        end
        else
        begin
            print 'updating property ' + @fullname + ' for ' + @table + '.' + @column;
            exec sys.sp_updateextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level2type=N'COLUMN',
                @level1name=@table, @level2name=@column, @value=@newvalue
        end
    end
    else if @action = @action_delete
    begin
        if @column is null
        begin
            print 'dropping property ' + @fullname + ' for ' + @table;
            exec sys.sp_dropextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
                @level1name=@table
        end
        else
        begin
            print 'dropping property ' + @fullname + ' for ' + @table + '.' + @column;
            exec sys.sp_dropextendedproperty @name=@fullname, @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level2type=N'COLUMN',
                @level1name=@table, @level2name=@column
        end
    end
    fetch next from mergeList into @table, @column, @name, @newvalue, @action;
end
close mergeList;
deallocate mergeList;

/*
-- see existing extended props:
declare @prefix varchar = 'NamespaceHere_'
select
    tbl.name [table],
    col.name [column],
    substring(ep.name,7,1000) name,
    ep.value [value]
from sys.extended_properties ep
    inner join sys.objects tbl on tbl.object_id = ep.major_id
    left outer join sys.columns col on col.object_id = ep.major_id and col.column_id = ep.minor_id
where ep.name like @prefix + '%'
order by tbl.name, ep.minor_id

-- output in a format suitable for inclusion in this script as a reference copy for source-control
-- run it, copy paste the result into the above insert into @properties, swap the last comma for a semi-colon.
-- don't use results-as-text as it truncates long values.
declare @prefix varchar = 'NamespaceHere_'
select
    '(''' + 
        tbl.name +
    ''', ' + 
        iif(col.name is null, 'null', '''' + col.name + '''') +
    ', ''' + 
        substring(ep.name,7,200) +
    ''', N''' + 
        replace(cast(ep.value as nvarchar(max)), '''', '''''') +
    '''),'
from sys.extended_properties ep
    inner join sys.objects tbl on tbl.object_id = ep.major_id
    left outer join sys.columns col on col.object_id = ep.major_id and col.column_id = ep.minor_id
where ep.name like @prefix + '%'
    and tbl.name not like '\_\_%' escape '\' -- ignore ready-roll migration tracking
    and tbl.name not like 'ast%' -- ignore the generated enum tables, they already have their own values
order by tbl.name, ep.minor_id, ep.name
*/

--commit
--rollback

ms_description variation https://gist.github.com/timabell/6fbd85431925b5724d2f (less general)

If you like that you might like this https://blog.timwise.co.uk/2018/03/12/developers-love-your-sql-database/