SQL Server Performance

Document a Database

Discussion in 'Third Party Tools' started by CanadaDBA, Jun 9, 2004.

  1. CanadaDBA New Member

    How can I document a DB? Is it a must to use third party utility or it's possible (but probebly hard) to use SQL-Server commands?

    Farhad R
    CanadaDBA@Yahoo.ca
  2. Raulie New Member

    You can pull most all metadata from SQL commands, most third party utilities use these commands they just make the reports look all nice and pretty. You guys must have no budget [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Maybe we can help though, what information do you want to pull from SQL??
  3. Luis Martin Moderator

    I suggest to see ours sponsors, SQL Scribe is very good tool.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. CanadaDBA New Member

    I need tables definitions, Fields descriptions, including PK and tables relations. <br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Lazy_DBA</i><br /><br />You can pull most all metadata from SQL commands, most third party utilities use these commands they just make the reports look all nice and pretty. You guys most have not budget [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Maybe we can help though, what information do you want to pull from SQL??<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />CanadaDBA
  5. Raulie New Member

    You make it to easy....query the INFORMATION.SCHEMA VIEWS that SQL Server provides it will give you all the info you are requesting.

    Look up INFORMATION.SCHEMA in BOL and you'll see what I'm talking about.
  6. satya Moderator

    True, usage of INFORMATION.SCHEMA views is ideal than going towards system tables and I agree what Lazy suggest in part of third party tools.

    Consider using GENERATE SQL Sciprts in order to get complete database schema operation.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Raulie New Member

    FarhadR

    In response to you email here is the query to pull Column descriptions not found in INFORMATION.SCHEMA.columns

    Use MyDatabase
    SELECT sysobjects.name AS [Table], syscolumns.name AS [Column], sysproperties.[value] AS Description
    FROM (sysobjects INNER JOIN syscolumns
    ON syscolumns.id = sysobjects.id) LEFT JOIN sysproperties
    ON sysproperties.smallid = syscolumns.colid AND
    sysproperties.id = syscolumns.id
    where sysobjects.name = 'TableName'

    If used for Ad-Hoc purposes there shouldn't be a problem query system tables, but like Satya said it is not good practice, and it shouldn't be included in user code.
  8. CanadaDBA New Member

    Good! Thank you for your code. I'm going to use it to prepare my documentation. Thank you all guys.

    CanadaDBA
  9. Wingenious New Member

    If you would like to go beyond simple query results and generate a detailed reference, consider WT3 - one of the SQL Server Tools applications...

    http://www.sqlservertools.us

    It will do what you have requested, as well as a lot more (for a fraction of the cost of other products).

  10. Raulie New Member

    quote:Originally posted by Wingenious

    If you would like to go beyond simple query results and generate a detailed reference, consider WT3 - one of the SQL Server Tools applications...

    http://www.sqlservertools.us

    It will do what you have requested, as well as a lot more (for a fraction of the cost of other products).



    Ok, I just installed your trial software to audit, it just generated a simple report on all my objects in the DB, oh and dependancies, am I missing a Service Pak or something? what happened to the detailed reports[?]

  11. Wingenious New Member

    quote:Ok, I just installed your trial software to audit, it just generated a simple report on all my objects in the DB, oh and dependancies, am I missing a Service Pak or something? what happened to the detailed reports

    The tone and content of your posting does not seem like you are interested in actually evaluating the product on the relative merits.

    The initial screen IS simply a listing of objects, but there are several options beyond that listing. The most relevant is an option to generate a system of HTML pages. The pages contain table definition details, the very things that the original poster mentioned. The pages also contain object dependency details. Everything is indexed and linked. There are no printed reports. The original poster did not mention printed reports. One could consider a printed HTML page a report, but the results of a query are certainly not a report.

    In addition, the same application can list dependencies for a selected object, find any text within the SQL code of objects, restore object dependency records, and produce a script file for selected objects. Many of these features are often requested in the forums. The WT3 documentation features alone seem to be quite similar to the feature set of the other product mentioned in this thread. Even with the additional functionality, WT3 is currently listed at a price that is 1/20 (5%) of the other product.

  12. Raulie New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FarhadR</i><br /><br />How can I document a DB? <b>Is it a must to use third party </b>utility or it's possible (but probebly hard) to use SQL-Server commands?<br /><br />Farhad R<br />CanadaDBA@Yahoo.ca<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Is it a must to use third party? <b>NO</b><br />Or is it possible (but probably hard) to use SQL-Server Commands? <b>Possible, and not hard</b><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Wingenious</i><br />The tone and content of your posting does not seem like you are interested in actually evaluating the product on the relative merits.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />I did evaluate the product, I pass [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], but I don't discourage anyone from insalling the software and checking it out for themselves.<br />
  13. Wingenious New Member

    If the needs are simplistic then a simple query is all that's necessary. If the needs are more demanding then a robust utility can be very helpful. WT3 is a robust utility, as is SQL Scribe. There is an example of the WT3 documentation output on the SQL Server Tools web site. WT3 does nearly everything SQL Scribe does, plus it adds a few other tricks. According to the SQL Scribe web site that product costs $400. WT3 is currently listed at $19 for a single user. Many of the other SQL Server Tools applications have a similar value advantage over well-known utility products.
  14. satya Moderator

    Folks
    As most of the thread discussion is related to a third party tool, will move to appropriate section.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. Raulie New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Wingenious</i><br /><br /> WT3 is currently listed at $19 for a single user. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />However I must admit that the price is not to argue for. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  16. blockwood New Member

    don't forget that ApexSQL is also site sponser! - we offer a tool ApexSQL Dochttp://www.apexsql.com/sql_tools_doc.htm

    it produces html help file output, GUI or command line, wizard like interface. $199 with free updates / free support

    it is relatively new but we have seen strong initial sales of it despite established competitors.

    Brian Lockwood
    ApexSQL Software
    http://www.apexsql.com
    Value Added SQL Server Tools
  17. fhanlon New Member

    seehttp://www.sqlservercentral.com/scripts/contributions/1005.asp for a script to document your database using HTML. You can make whatever modifications you want. For example I didn't like the column display so I changed it to:

    DECLARE cursor_Column CURSOR FOR
    SELECT syscolumns.[name], systypes.[name] ,
    syscolumns.length,
    sysproperties.[value],
    --syscolumns.prec,
    syscolumns.scale,
    CASE syscolumns.isnullable
    WHEN 1 THEN 'YES'
    ELSE 'NO'
    END AS 'NULLABLE',
    syscolumns.[collation]
    FROM sysobjects
    INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON systypes.xtype = syscolumns.xtype AND syscolumns.xusertype = systypes.xusertype --AND syscolumns.type = systypes.type
    LEFT OUTER JOIN sysproperties ON syscolumns.colid = sysproperties.smallid
    AND syscolumns.id = sysproperties.id
    WHERE sysobjects.id = @table_id ORDER BY syscolumns.colorder

    This nice thing is you can make whatever enhancements you want. Of course there are a lot of advantages to getting a 3rd party product that may be easier for more people to use especially those unfamiliar with T-SQL.
  18. rbedick New Member

    You might want to try SchemaToDoc at http://www.schematodoc.com. It's output is a Word document that includes information about your tables (primary keys), fields (type, size, defaults, nullable), indexes, constraints, foreign key constraints, stored procedures, and views. It also lets you annotate your tables and fields, and include those annotations in the Word output.
  19. wendybo New Member

    SqlRaptor produces html,chm, and rtf file. It is distribuited in combination with a compare tool.

    www.miqui.it
  20. Neo New Member

    I took a look at SQLRaptor and SchematoDoc. Not too impressed with output and I am wondering about other opinions. They seem to be running artound $200. Does anyone think this reasonable? What would you pay for a good quality documenter? Thanks.

    Thomas

    "Okey dokey... free my mind. Right, no problem, free my mind, free my mind, no problem, right... "
  21. JAG2DB New Member

    You might want to try dbdesc. It generates documentation in different formats like HTML, WORD, RTF, PDF, XML and you can even customize the output using XSL templates. It costs only $79.95.

    Reviewed here: http://www.larkware.com/Reviews/dbdesc.html

Share This Page