Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: Adding comments to metadata

    Did some searching and didn't seem to find what I'm looking for. I'm pretty new to SQL Server (most of my experience is on DB2 for z/OS).

    I'm building some new tables, and want to find a way to add comments to the metadata for the column. In DB2 the syntax is:

    COMMENT ON COLUMN TB_CREATOR.TB_NAME.COLUMN_NAME IS 'comments here';

    OR

    COMMENT ON TB_CREATOR.TB_NAME (
    COLUMN1 IS ' comment here',
    COLUMN2 IS ' comment here',
    );

    Is there anything like this in SQL Server?

    Thanks!

  2. #2
    Join Date
    Mar 2008
    Posts
    4
    anyone????

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    EXEC sys.sp_addextendedproperty
    @name
    =N'Caption'
    ,@value=N'Text I wish to Add'
    ,@level0type=N'SCHEMA',@level0name=N'dbo'
    ,@level1type=N'TABLE',@level1name=N'MyTable'
    ,@level2type=N'COLUMN',@level2name=N'MyField'
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The problem with this approach is, that there doesn't seem to be a way to actually retrieve that information again.

    The Visual Studio tools will display extended properties in a separate dialog (pretty clumsy to navigate there, at least with the Express version

    sp_columns and sp_tables will never return information in the "REMARKS" column of their result.
    The documentation either says "This field always returns NULL" (sp_columns) or "SQL Server does not return a value for this column" (sp_tables)

    So these "extended properties" seem to be pretty useless, as far as I can tell. At least not a replacement for the very handy ADD COMMENT that other DBMS (DB2, Oracle, Postgres) support.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    most tools expect the name of the property to be MS_Description. if you name them with that name, they will show up in the Description fields of various tools, like SSMS, VS, etc.

    also SqlSpec will pick them up - see link in my sig.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jezemine
    most tools expect the name of the property to be MS_Description. if you name them with that name, they will show up in the Description fields of various tools, like SSMS, VS, etc.

    also SqlSpec will pick them up - see link in my sig.
    Thanks for the insight.

    I still don't understand why the documented stored procs from Microsoft don't return comments at all.
    I'm mostly working in a Java/JDBC environment which makes these comments practically useless.

  7. #7
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Try using fn_listextendedproperty
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by GWilliy
    Try using fn_listextendedproperty
    Thanks for the hint, but it doesn't really help me as the MS JDBC driver is using sp_tables and sp_columns to retrieve the information and those - as documented - will never return comments.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    sounds like you are complaining more about the implementation of the JDBC driver then.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by jezemine
    sounds like you are complaining more about the implementation of the JDBC driver then.
    Well a bit, but to a certain extent also about the stored procedures that should be used to retrieve that information. sp_tables and sp_columns are intended for this and they simply do not return comments. The JDBC driver(s) simply make use of those procedures.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •