Post reply

Descriptive Comments for a Column

  • June 13, 2007 at 12:43 pm

    #196403

    Hi All

    I'm trying to find a way to add descriptive comments to a column as part of the DB schema. We have a product (based on SQL 2005) that we develop and sell and we often have to give descriptions of tables to customers for them to prepare data for import (prior to install).

    I can get all the details of the columns with sp_help '<tablename>'

    However we then have to add a description to each line (for example: StateID: "Foreign key for "States table". (This is obvious, but many are not.)

    What I'd like to do (somehow) is store "Foreign key for "States table" with the column StateID in the Address table. When columns are altered or new ones are added, a tech doc person will create/update the descriptive text. This will then allow us to auto-generate import documentation.

    Anyone got any ideas on the best way to do this?

    TIA

    Mark

  • June 13, 2007 at 12:48 pm

    #712726

    If I recall comments are extended properties. You can modify them using sp_addextendedproperty, sp_dropextendedproperty, sp_updateextendedproperty and view them using fn_listextendedproperty(). Some tools streamline modifying these, but that's the basic functions for setting and modifying those.

  • June 13, 2007 at 12:50 pm

    #712729

    Thanks a lot Aaron

    I'll have a play

    Regards

    Mark

  • June 14, 2007 at 6:49 pm

    #713097

    Dunno how it works in 2k5, but in 2k, you could open Enterprise Manager, right click on a table and select "Design Table"... the "Description" for each column is where you want to enter such things...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 14, 2007 at 9:02 pm

    #713111

    Thnaks a lot Jeff

    Found it. Now I can do it programmatically though it's way better

    Regards

     

    mark

  • June 15, 2007 at 12:06 am

    #713144

    you might also want to check out SqlSpec (in my sig below).  with it you can generate docs for any database, and also edit the extended properties for all objects directly from the docs (including all table and view columns). 

    the idea being, you can generate the docs as a chm and the hand the chm to a technical writer who fills in all the comments.

    ---------------------------------------
    elsasoft.org

  • June 15, 2007 at 3:35 pm

    #713412

    Hi Guys

    I created a command for adding the extended properties (I added on called "Description"):

    exec

    sp_addextendedproperty @name = N'Description', @value = 'Primary Key' , @level0type = N'schema', @level0name = dbo, @level1type = N'Table', @level1name = LookupSalesPerson, @level2type = N'Column', @level2name = ID;

    I also have an sp for getting them out.

    However, I noticed that in SQL 2005 you can modify a table, select a column and then you can enter a Description there too. (see attached)

    My sp to retrieve the extended properties als retrieves anything set in the regular column property called Description. However, I can't figure out a way to set the regular column property programmatically.

    Anyone have any ideas?

    TIA

    MArk

     

  • June 15, 2007 at 7:25 pm

    #713429

    you should name the property MS_Description, not Description.  if you do that, your comment will be picked up by the MS tools like SSMS, etc.

    ---------------------------------------
    elsasoft.org

  • June 15, 2007 at 8:35 pm

    #713434

    Thanks a lot Jezemine

    Work perfectly

    Regards

    Mark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply