Document a Database

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

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='' />]<br /><br />Maybe we can help though, what information do you want to pull from SQL??

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='' />]<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

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.

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.

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

CanadaDBA

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).

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[?]

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.

<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 />

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.

<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='' />]

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

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.

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.

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

www.miqui.it

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... "

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