none
How to Copy one Column Extended Property to another?

    Question

  • Hello MSDN,

                    How can I Copy one extended property of a column to another?

                    Please let me explain the requirement.

                    1)I have a database Column say Column1, Which have 3 extended properties Say prop1, prop2 and prop3.

                    2)I want to copy the value of prop2 and prop3 to prop1 of the same column(Column1).

                   After this 

                   3) I want to traverse through all the COLUMN objects of the database and do the same (step 2)

    Is there any one have any idea, how to implement this on a StoredProcedure or function?

    or using a C# application?

    salampv.07

    Friday, July 20, 2012 9:14 AM

Answers

  • It's easy to do this manually: Just script the table in Management Studio and the extended properties come with it:

    EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'DimCustomer', N'COLUMN', N'CustomerKey'

    To get the extended properties of all tables of a given database, you can query sys.extended_properties. So one solution would be to create a cursor on this management view, and for every row make a call to the sp_addextendedproperty procedure.

    If you prefer .Net code, you can always use SMO to itterate over the tables, use the Scripter class to script every table, and then filter out anything but the sp_addextendedproperty calls...

    Hope this helps...


    Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net, @SQLWaldorf (twitter)

    Friday, July 20, 2012 9:26 AM

All replies

  • It's easy to do this manually: Just script the table in Management Studio and the extended properties come with it:

    EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'DimCustomer', N'COLUMN', N'CustomerKey'

    To get the extended properties of all tables of a given database, you can query sys.extended_properties. So one solution would be to create a cursor on this management view, and for every row make a call to the sp_addextendedproperty procedure.

    If you prefer .Net code, you can always use SMO to itterate over the tables, use the Scripter class to script every table, and then filter out anything but the sp_addextendedproperty calls...

    Hope this helps...


    Dr. Nico Jacobs, SQL Server BI trainer @ U2U.net, @SQLWaldorf (twitter)

    Friday, July 20, 2012 9:26 AM
  •  Hello SLQWaldorf,

                      Thank you very much for your reply.

                      I am not aware about creating cursors and triggers, Could you give me a sample for this.

                      I have started a query, but it does not execute,Could you please suggest a solution for the Error in below T-SQL

    EXEC sys.sp_updateextendedproperty
    @name = N'prop1',
    @value = (select value from fn_listextendedproperty(NULL, N'SCHEMA', 'dbo', N'TABLE', N'Table1', N'COLUMN',N'Column1') where name = N'prop2'),
    @level0type = N'SCHEMA', @level0name = [dbo] ,
    @level1type = N'TABLE',  @level1name = N'Table1',
    @level2type = N'COLUMN', @level2name = N'Column1';

     Regards

    Salam


    salampv.07

    Friday, July 20, 2012 9:48 AM