7

The questions really says it all, Is it possible to update an extended property of a column in a table. I have been looking around online but they only seem to show updating the extended property for a table and not the columns in a table.

Ben
  • 245
  • 4
  • 13

1 Answers1

6
EXECUTE sp_updateextendedproperty 
N'MS_Description', 
@v, 
N'SCHEMA', N'dbo', 
N'TABLE', N'Table_1', 
N'COLUMN', N'i'

It's actually the very first sample in MSDN topic:

http://technet.microsoft.com/en-us/library/ms186885.aspx

Here's a more complete sample:

--Add extended property
EXEC sp_addextendedproperty 
    @name = N'Question1'
    ,@value = N'Hello'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = Acceptance
    ,@level2type = N'Column', @level2name = P101;
GO
--Verify
SELECT * FROM fn_listextendedproperty
(NULL, 'schema', 'dbo', 'table', 'Acceptance', 'column', 'P101');
GO
--Update the extended property.
EXEC sp_updateextendedproperty 
    @name = N'Question1'
    ,@value = N'Hello, What is your name'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = Acceptance
    ,@level2type = N'Column', @level2name = P101;
GO
--Verify
SELECT * FROM fn_listextendedproperty
(NULL, 'schema', 'dbo', 'table', 'Acceptance', 'column', 'P101');
GO
dean
  • 9,960
  • 2
  • 25
  • 26
  • Hi thanks for your answer. I have just seen this and wonder why i need Description and Scheme. Can I do this `exec sp_AddExtendedProperty 'TABLE', @tableName , 'COLUMN', @columnName , 'NAME', @name , 'VALUE' , @value` – Ben Apr 03 '14 at 09:55
  • No, "References to an object in one level must be qualified with the names of the higher level objects that own or contain them. " – dean Apr 03 '14 at 09:57
  • To avoid ambiguity, it would be good idea to specify the schema level as well. The 'MS_Description' in my answer was simply a name of the property. – dean Apr 03 '14 at 10:18
  • I know it will sound stupid but I am trying to put this into a query and its saying I'm supplying `invalid parameters`. If I had a Table named `Acceptance` and a Column Named `P101` and then I wanted the name of the Property `Question1` and then the Value `Hello, What is your name` where would I add them in the query. – Ben Apr 03 '14 at 10:24