SQL Server manage column descriptions by script

Question

Is there a way to alter/update and drop/delete column descriptions via script?

I use sp_addextendedproperty to add description, but it does not allow update. When I try to use same sp to update an existing description value, it says something like "description property already exists"

Both alter or drop/create like solutions are ok for me.

SOLUTION

After helpful answers and comments, you can see my final solution below. May help someone.

create procedure sp_set_column_description (
    @schema varchar(256),
    @table varchar(256),
    @column varchar(256),
    @description varchar(256))
    as
begin
    if exists (
        select p.* 
        from
            sys.extended_properties p, 
            sys.columns c, 
            sys.tables t, 
            sys.schemas s
        where
            t.schema_id = s.schema_id and
            c.object_id = t.object_id and
            p.major_id = t.object_id and
            p.minor_id = c.column_id and
            p.name = N'MS_Description' and 
            s.name = @schema and
            t.name = @table and
            c.name = @column
    )
        exec sys.sp_updateextendedproperty 
            @level0type=N'SCHEMA', @level0name=@schema,
            @level1type=N'TABLE', @level1name=@table,
            @level2type=N'COLUMN', @level2name=@column,
            @name=N'MS_Description', @value=@description
    else
        exec sys.sp_addextendedproperty 
            @level0type=N'SCHEMA', @level0name=@schema,
            @level1type=N'TABLE', @level1name=@table,
            @level2type=N'COLUMN', @level2name=@column,
            @name=N'MS_Description', @value=@description
end

go

create procedure sp_drop_column_description (
    @schema varchar(256),
    @table varchar(256),
    @column varchar(256))
    as
begin
    if exists (
        select p.* 
        from
            sys.extended_properties p, 
            sys.columns c, 
            sys.tables t, 
            sys.schemas s
        where
            t.schema_id = s.schema_id and
            c.object_id = t.object_id and
            p.major_id = t.object_id and
            p.minor_id = c.column_id and
            p.name = N'MS_Description' and 
            s.name = @schema and
            t.name = @table and
            c.name = @column
    )
        exec sys.sp_dropextendedproperty 
            @level0type=N'SCHEMA', @level0name=@schema,
            @level1type=N'TABLE', @level1name=@table,
            @level2type=N'COLUMN', @level2name=@column,
            @name=N'MS_Description'
end

I believe there is a corresponding update proc: `sp_updateextendedproperty` — Steph Locke, Jun 13 '13 at 12:07
Seems like there are `sp_updateextendedproperty` and `sp_dropextendedproperty`. I will try them. — Mehmet Ataş, Jun 13 '13 at 12:09
`sp_updateextendedproperty` http://msdn.microsoft.com/en-us/library/ms186885.aspx — Igor Borisenko, Jun 13 '13 at 12:46
drop and update procedures throw exception if property not exists and add sp throws exception if property already exists. I tried `try add catch update` logic but somehow it causes a database lock. After executing a 'rollback' lock disappeared but dealing with transactions for this simple operation is unnecessarily complicated. It would be better we could check if extended property exists. — Mehmet Ataş, Jun 13 '13 at 13:39
Would [sys.fn_listextendedproperty](http://msdn.microsoft.com/en-us/library/ms179853.aspx) help? — Richard Deeming, Jun 13 '13 at 13:50
Thanks for the useful answer. [Here is a slight variation](https://gist.github.com/tom-haines/e498fb0bde88991dde9446053d854780#file-util_set_comments-sql) that follows the current schema (i.e. uses `select @SchemaName = SCHEMA_NAME()`). — ThomasMH, Jul 31 '21 at 07:30

Kevin Suchlicki · Accepted Answer · 2013-06-13T15:29:29.650

7

Combined with Steph Locke's suggestion, you can check for existence of the extended proc using:

if exists(
    SELECT  * 
    FROM    sys.extended_properties p
            join sys.columns c on p.major_id = c.object_id and p.minor_id = c.column_id 
    where   p.major_id = OBJECT_ID('yourtablename','table')
            and p.name = 'Description'  
)

edited Jun 13 '13 at 15:29

answered Jun 13 '13 at 14:37

Kevin Suchlicki

3,096
2
15
17

2

Why `SELECT top 1 1`? It looks horrible and is no better than `SELECT *` – Martin Smith Jun 13 '13 at 14:42
Habit. I do that when creating a semi-join with "exists." I am under the impression that in *that* case, it makes a performance difference. In the above example, it clearly doesn't. – Kevin Suchlicki Jun 13 '13 at 15:03
@Martin Smith Interesting stuff, thank you for that. I try to avoid unecessary fluff in my SQL code, so I will scratch that one. Edited my answer. – Kevin Suchlicki Jun 13 '13 at 15:26

SQL Server manage column descriptions by script

SOLUTION

1 Answers1

Linked