Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
source-controllable self-enforcing extended attribute list for sql server
-- 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
@timabell

This comment has been minimized.

Copy link
Owner Author

@timabell timabell commented Mar 16, 2018

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

@timabell

This comment has been minimized.

Copy link
Owner Author

@timabell timabell commented Mar 26, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment