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

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

commented Mar 26, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.