Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unanswered: Table level Comments

    Is there a way to add comments on a table in sql server?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You can do it in EM....plus there's a system proc that let's you add and retreive them...more painful than anything else...

    What do you want to do?

    Create a data dictionary?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    35
    Yes, I want to create a data dictionary. What is the stored proc called?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I can't remember right now...never use it....

    I make the dictionary right in the database so I can join them to the information_schema views...

    It's more flexible

    AND you create one for columns...which I don't think you can in sql server..


    Something like...

    Code:
    USE Northwind 
    GO
    
    CREATE TABLE TABLE_DEFS(TABLE_NAME sysname, Definition varchar(7500))
    GO
    
    CREATE TABLE COLUMN_DEFS(TABLE_NAME sysname, COLUMN_NAME sysname, Definition varchar(7500))
    GO
    
    INSERT INTO TABLE_DEFS(TABLE_NAME) 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
    
    INSERT INTO COLUMN_DEFS(TABLE_NAME, COLUMN_NAME) 
    SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM TABLE_DEFS)
    GO
    
    DROP TABLE TABLE_DEFS
    DROP TABLE COLUMN_DEFS
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    sp_addextendedproperty
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    Brett... I believe the sp_addextendedproperty will add comments on columns and tables.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK....but do you use it?

    Do you find of much value?

    Where do you're data modelers store their dictionary?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I use it. I use it for every database I design. It's like having a built-in documentation. And it's being stored in dtproperties, so it's also part of the database. And with data diagrams you can have modular design for every bit of your structure. The use is either implicit (through EM and data diagram) or explicit, from QA or OSQL script.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've known about it but never used it. Like Brett I keep a separate data dictionary. I actually use a separate database that has routines for storing metadata on all the other databases on the server. One of the routines will read the table descriptions, but I don't write to them.

    Rdjabarov, what method do you use for editing the descriptions, and what policies do you encourage to get developers to use them?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    PLUS...there's so much more flexibility if you manage it yourself....

    What about overhead....

    Plan to bang away at system tables in prod environment?

    PLUS...I'm not sure where this stuff is stored...why a system sp?

    Why not part of the CREATE Statement like in DB2?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Sep 2003
    Posts
    364
    The tool I use for modeling and maintaining my data dictionary has the ability to call sp_addextendedproperty to keep the comments in the db up to date with the data dictionary. If I didn't have this I would never use it, sp_addextendedproperty that is.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the tool?

    I use ERWin almost exclusivley, and my own concoction(s) for modeling purposes...

    Do you do any business process modeling, data flow diagrams as well?

    I set it up so they're all interconnected....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Sep 2003
    Posts
    364
    Yep I do all of that and I use PowerDesigner. I previously used Erwin but would recommend PowerDesigner to anyone. It's very robust. It's probably the best product Sybase offers. The coolest thing is you can customize every aspect of it and Sybase provides a the object model for PowerDesigner so you can write java, vbscript, etc to process against everything. For example, I've customized some vbscript to work with PD's object modeler that will take the entire datamodel and generate the object model for the data access layer of all of our java apps. This way I have complete control over any statement executed against the db. And this is just for starters.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No kidding...I'll take a look...

    what's the price tag?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Sep 2003
    Posts
    364
    It's around $1,500 per module per seat. I think there's three modules data & logical modeler, object modeler, and business process modeler. Of course the mode modules you buy the better the price. They have some cool XML and Free modeling too. I'm not sure which module or modules it comes with.

Posting Permissions

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