locked
Extended properties RRS feed

  • Question

  • I'm just playing around with the product and noticed that the generated update script includes drop statements for extended properties which we use for column, table and constraint descriptions.

    Just had a quick look throguh the documentation, but couldn't find anything about it, is there some sort of support or a best practice to handle them?

    TIA

    Tuesday, June 13, 2006 2:15 PM

Answers

  • Hi Gianluca,

    I think you may have run into something which didn't make it into CTP3 -

    Import Database Schema is currently not adding extended properties.  For deploying a project, there is a workaround:

    - Add all of the sp_addextendedproperty into the postdeployment.SQL file in the project

    - Deploy via the project (set the project properties, and then right-click on the project and select 'Deploy Selection'

    Hope this helps a little,

    -Tom

     

    Wednesday, June 14, 2006 8:42 AM

All replies

  • Hi Gianluca,

    I think you may have run into something which didn't make it into CTP3 -

    Import Database Schema is currently not adding extended properties.  For deploying a project, there is a workaround:

    - Add all of the sp_addextendedproperty into the postdeployment.SQL file in the project

    - Deploy via the project (set the project properties, and then right-click on the project and select 'Deploy Selection'

    Hope this helps a little,

    -Tom

     

    Wednesday, June 14, 2006 8:42 AM
  • Hi Tom,
    thanks for the answer, I realized that 2005 is not supported (yet) just after posting.

    Beside the workaround you suggested, I'd like to know (roughly) how extended properties will be implemented in RTM in order to be able to organize myself (we are using a small project as a pilot).

    I noticed that right now you can write the T-SQL call to add them directly in each object's script (no errors). For example:

    -- begin table script --

    CREATE TABLE [dbo].[Table1]
    (
     column_1 int NOT NULL,
     column_2 int NULL
    )

    -- add table's extended properties
    EXEC sp_addextendedproperty
     'MS_Description', 'Description for table1'
    , 'user', 'dbo'
    , 'table', 'Table1'

    -- add columns's extended properties
    EXEC sp_addextendedproperty
     'MS_Description', 'Description for column 1'
    , 'user', 'dbo'
    , 'table', 'Table1'
    , 'column', 'column_1'

    -- end table script --

    -- begin table constraint script

    ALTER TABLE [dbo].[Table1]
     ADD CONSTRAINT [ck_Table1]
     CHECK  (column_1 > 0)

    EXEC sp_addextendedproperty
     'MS_Description'
    , 'Column 1 must be greater than zero (Rxxx)'
    , 'user', 'dbo'
    , 'table', 'Table1'
    , 'constraint', 'ck_Table1'

    -- end table constraint script

    I'm wondering if this is how extended properties will be handled or if they will be handled in a completely different manner like in a single file or using tags inside comments (I personally like this last option).

    Wednesday, June 14, 2006 9:20 AM
  • We use many extended properties on the database as well as each table.  The various extended properties allow us to add the correct header columns, followed by the correct triggers, stored procedures and compression method for the solution. 

    We are seriously considering using the new team database development ide.  We need to have extended property managed like other .Net code.

     

    Wednesday, June 14, 2006 2:08 PM
  • That's good feedback, we'll be looking at how best to store and manage extended properties shortly.
    Wednesday, June 14, 2006 7:58 PM
  • Add one more the request for extended properties. We use them fairly extensively within out databases.

    Also add to request the ability to store security with the objects, like GRANT EXECUTE on the stored procs (and yes, we're using the workaround for deployments right now).

    Wednesday, July 19, 2006 5:45 PM