Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Documenting tables/fields - utility?

    As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.

    Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.

    Thanks,

    Peter

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'll never understand what motivates people to want to take DATA from a DATABASE and store it in a SPREADSHEET.
    Tables and columns are already enumerated within the database, and a description of each column can be entered through Enterprise Manager.
    You can query the system tables to list all the tables with their columns and descriptions and (if you absolutely insist....) export the results to an Excel file.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Erwin is good for this. Which reminds me I am about a month and a half behind on that.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Hey - for a blind guy, you sure help me see the light.

    Thanks blindman.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I keep a bottle of hand-sanitizer with me for occasions when a client requires me to open up Excel, but otherwise all the data I deal with stays in databases.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    LOL - I like that line about having to use hand sanitizer...I will be using that one myself.

    I did find a quick query syntax at:

    http://searchvb.techtarget.com/tip/1...tml?bucket=ETA

    SELECT
    table_name=sysobjects.name,
    column_name=syscolumns.name,
    datatype=systypes.name,
    length=syscolumns.length
    FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
    inner join systypes on syscolumns.xtype=systypes.xusertype
    where sysobjects.xtype='U'
    order by sysobjects.name,syscolumns.colid

    I altered the above to bring in the description that a user can enter against each field in the tables:

    SELECT
    table_name=sysobjects.name,
    column_name=syscolumns.name,
    datatype=systypes.name,
    length=syscolumns.length,
    descr=sysproperties.value
    FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
    inner join systypes on syscolumns.xtype=systypes.xusertype
    left join sysproperties on sysproperties.id=syscolumns.id and syscolumns.colid=sysproperties.smallid
    where sysobjects.xtype='U'
    order by sysobjects.name,syscolumns.colid


    This is fine for my immediate needs.
    Last edited by Pdiotte; 08-16-06 at 16:50.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    If you liked that one, you'll love this one ... and it's guarenteed not to become obsolete with sql server upgrades ...

    Code:
     
    select * from information_schema.columns

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Tom -

    Thanks - another good one for quick data generation.

Posting Permissions

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