SQLServerCentral / Discuss Content Posted by Raj Vasant / Article Discussions / Article Discussions by Author / Document Your Database / Latest Posts

RE: Document Your Database

SSIS Guy — Mon, 02 May 2011 11:48:28 GMT

Here is one more [url=http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Overview.aspx]Free Tool to document Sql Server[/url]

RE: Document Your Database

SSIS Guy — Mon, 02 May 2011 11:44:32 GMT

Here is one more Free Tool to [url=http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Overview.aspx]document Sql Server[/url]

RE: Document Your Database

Charles Kincaid — Wed, 02 Jan 2008 11:15:47 GMT

[quote][b]brhunter (1/2/2008)[/b][hr]Great advice. However, I think a better article would be on the practice of creating "Self Documenting" database Schemas. [/quote] See Joe Celko's book "SQL Programming Style" ISBN:0-12-088797-5

RE: Document Your Database

brhunter-540485 — Wed, 02 Jan 2008 09:35:44 GMT

Great advice. However, I think a better article would be on the practice of creating "Self Documenting" database Schemas.

RE: Document Your Database

Jonathan AC Roberts — Wed, 02 Jan 2008 09:11:37 GMT

I agree, I've gone to the effort of writing a Word macro template to document SQL-Server databases. You just select an ODBC data-source for a SQL server database and it will document all the tables, indexes, triggers, stored procedures and functions on the database. It's been well tested on sql-server 2005 but I think it should also run on 2000.If anyone would like to try it please email me at: dbdocument at gmail dot com. :)

RE: Document Your Database

Kris-420132 — Thu, 15 Nov 2007 14:54:16 GMT

Many thanks JoseThe script works and I found the views (I was looking under system tables before, never occurred to me to check the views)thanks againKris

RE: Document Your Database

JAG-360157 — Thu, 15 Nov 2007 11:05:49 GMT

Hi Kris,this is the same query for SQL 2005:[code]select SO.name as 'table', SC.name as field,ST.name as datatype, SC.max_length as 'size',sp.value as 'description'from sys.columns SC inner joinsys.objects SO on SC.object_id = SO.object_id inner joinsys.types ST on ST.user_type_id = SC.user_type_id left joinsys.extended_properties sp on sp.major_id = so.object_id and sp.minor_id = SC.column_idand sp.name = 'MS_Description'where SO.type = 'U'order by SO.name, SC.name[/code]Regarding your second question: [quote]if i wanted to browse these tables, how would I access them?[/quote]Just go to DB -> Views -> System viewsHope it helps.Regards,Jose

RE: Document Your Database

steve block — Thu, 15 Nov 2007 10:17:25 GMT

I haven't started using 2005 yet, but I'm sure someone on the forum can help you.Steve

RE: Document Your Database

Kris-420132 — Thu, 15 Nov 2007 09:37:58 GMT

I'm using sql 2005Do you by chance know how to access those objects in 2005?

RE: Document Your Database

steve block — Wed, 14 Nov 2007 15:04:31 GMT

The query works for me in SqlServer 2000. What version do you have?Steve

RE: Document Your Database

Kris-420132 — Wed, 14 Nov 2007 13:33:20 GMT

[quote][b]Andy DBA (1/11/2007)[/b][hr]CORRECTION. The prior posted sql may have duplicates. The following should work better:--List tables, columns and column descriptionsselect SO.name as 'table', SC.name as field, ST.name as datatype, SC.length as 'size', sp.value as 'description'from syscolumns SC inner joinsysobjects SO on SC.id = SO.id inner join systypes ST on ST.xusertype = SC.xusertype left join sysproperties sp on sp.id = so.id and sp.smallid = SC.colid and sp.name = 'MS_Description'where SO.xtype = 'U' and SO.status > 0order by SO.name, SC.name[/quote]When I try to run the query I get an error"Msg 208, Level 16, State 1, Line 2 Invalid object name 'sysproperties'."If i remove the sysproroerties table from the query the query will run, for some reason its the only table that I can't find.Furthermore I logged in as SA to see if it was a permissions error, but I still got the same error.Next question (could be linked to the problem above), if i wanted to browse these tables, how would I access them?My first guess, using the SQL SMS would be to go DB -> Tables -> System Tables -> syspropertiesThanks in advanceKrisHowever the only table in "System Tables" is sysdiagrams

RE: Document Your Database

David.Poole — Fri, 02 Feb 2007 11:49:00 GMT

You can also right-click on the object in the object tree within SQL Query Analyser

RE: Document Your Database

steve block — Thu, 01 Feb 2007 15:40:00 GMT

For those of you who want to use the extended properties and think that there is no extended properties for the table in general, there is a way to enter it directly from Enterprise Manager.

When you are in Design Table, right click one of the columns and chose Properties. The field called Description on the Tables tab is stored in the extended properties.

You can access it from a script with:

SELECT @tabledesc = value FROM ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', @tblname, DEFAULT, DEFAULT)

I have a script called usp_Table_Doc that takes the table name as a parameter outputs a file layout, complete with the extended properties as descriptions, if anyone is interested.

Steve

RE: Document Your Database

jezemine — Thu, 18 Jan 2007 13:19:00 GMT

also sqlspec does more than just SQL Server. Oracle, MySQL, Access, Analysis Services are supported.DB2, Sybase, PostgreSQL coming soon...

RE: Document Your Database

Kiran-396897 — Fri, 12 Jan 2007 15:18:00 GMT

This idea seems to be good.

RE: Document Your Database

David.Poole — Fri, 12 Jan 2007 11:50:00 GMT

Don't forget Innovasys DocumentX. It covers SQL Server, Oracle, Access and also does .NET assemblies, Visual Studio projects, ActiveX and VBA.After using a proper tool I wouldn't use a word processor for documentation because

It is not easy to update
It is not intrinsically linked to the database so can be obsolete fast
Does not automatically cross reference to bookmarks within itself
Isn't a natural reference format (such as CHM or web ) for programmer
Is very bulky

It sounds obvious but the purpose of documentation is to be read. There is a great skill in writing a complex document in such a way that it is easy to pick up the salient facts quickly.

RE: Document Your Database

JAG-360157 — Fri, 12 Jan 2007 08:38:00 GMT

dbdesc (mentioned earlier in this thread) can generate Word 2003 and RTF documents among others. It uses XSL templates so the output can be fully customized.Additionally, it includes a built-in extended property editor to easily manage descriptions. In my opinion, extended properties are the perfect place to store your comments as they go together with your database.

RE: Document Your Database

Robert Bedick — Fri, 12 Jan 2007 05:11:00 GMT

It's great that you mentioned the Apex product, but I would expect an "article" on the topic to do a more thorough compare-and-contrast of the many products that are out there to help a dba document his or her database. Just to mention some others:

http://www.schematodoc.com/http://www.ag-software.com http://www.dbdesc.com

RE: Document Your Database

Terrie — Thu, 11 Jan 2007 12:35:00 GMT

I keep a corresponding dictionary database for every database I design with descriptions of the tables, views and columns. Good idea to add stored procs, I hadn't thought of that. I'm required to document data classification (you know what's sensitive and what's not), so that's in there too. For that last database I developed, all the columns are populated from a non-windows system so I added the source information too. Now I can talk to the admins on that system in their terms if I need to.

RE: Document Your Database

Charles Kincaid — Thu, 11 Jan 2007 11:42:00 GMT

I added this:

select SO.name as 'table', SC.name as field,

ST.name as datatype, SC.length as 'size',

COALESCE(sp.value,df.Comment,'') as 'description'

from syscolumns SC

inner join sysobjects SO on SC.id = SO.id

inner join systypes ST on ST.xusertype = SC.xusertype

left join sysproperties sp on sp.id = so.id and sp.smallid = SC.colid and sp.name = 'MS_Description'

left outer join doc_fields df on df.tablename = so.name and df.fieldname = SC.name

where SO.xtype = 'U' and SO.status > 0

order by SO.name, SC.colorder

Now it looks into the extended properties or my table.

RE: Document Your Database

Charles Kincaid — Thu, 11 Jan 2007 11:24:00 GMT

You are right. Just tried it in 2005 and it fails.

RE: Document Your Database

Andy DBA — Thu, 11 Jan 2007 09:26:00 GMT

CORRECTION. The prior posted sql may have duplicates. The following should work better:

--List tables, columns and column descriptionsselect SO.name as 'table', SC.name as field, ST.name as datatype, SC.length as 'size', sp.value as 'description'from syscolumns SC inner joinsysobjects SO on SC.id = SO.id inner join systypes ST on ST.xusertype = SC.xusertype left join sysproperties sp on sp.id = so.id and sp.smallid = SC.colid and sp.name = 'MS_Description'where SO.xtype = 'U' and SO.status > 0order by SO.name, SC.name

RE: Document Your Database

Andy DBA — Thu, 11 Jan 2007 09:07:00 GMT

This may not have been mentioned in the article because it's too obvious, but I consider a database relational diagram to be one of the most important pieces of documentation for any database. I usually create one with only the "core" tables but detailed column information and another with all tables including "look-up"s but with less column detail.

I also use the following sql to list tables, columns, and the "Description" text that EM's "Design Table" lets you enter. I save the results to Excel and add the spreadsheet to my database documentation.

Note: This works on SQL 2000. I have not tried it on SQL 7.0 or 2005 (yet) but it probably needs adjusting because it uses system tables.

--List tables, columns and column descriptionsselect SO.name as 'table', SC.name as field, ST.name as datatype, SC.length as 'size', sp.value as 'description'from syscolumns SC inner joinsysobjects SO on SC.id = SO.id inner join systypes ST on ST.xusertype = SC.xusertype left join sysproperties sp on sp.id = so.id and sp.smallid = SC.colidwhere SO.xtype = 'U' and SO.status > 0order by SO.name, SC.name

RE: Document Your Database

Charles Kincaid — Thu, 11 Jan 2007 08:22:00 GMT

I've been doing this for quite a while now. Version before 2005 did not support extended properties for tables well. I wanted a unified system. So:

CREATE TABLE [doc_Fields] ( [TableName] [varchar] (50), [FieldName] [varchar] (50), [Comment] [varchar] (4000)) ON [PRIMARY]

I then put my field and table descriptions here. If the field name is blank, or NULL, then Comment applies to the table. A little grid based program to make the entry easy and one to generate an RTF file.

Now ported to .Net and using the extended properties it serves us well. The advantage of the extended properties is that when I generate create scripts the comments come along. This is also a help to the UI teams. They can use this to generate tool tips.

RE: Document Your Database

Mark Harr — Thu, 11 Jan 2007 07:26:00 GMT

I recently saw a pretty good product for a affordable price. SQLSpec from Elsasoft (http://www.elsasoft.org/) was actually written by a member here http://www.sqlservercentral.com/forums/userinfo.aspx?id=145142. It does not do HTML Help files like ApexDoc, but seems to cover the features pretty well. It can also help to document application access (though you have to put the links and info in via an additional XML file). And for $50, it certainly more affordable than the others listed here.

RE: Document Your Database

Tim Mackey — Thu, 11 Jan 2007 06:41:00 GMT

From my experience it's not just about documenting the objects, but how they're accessed and their purpose; and ensuring that the documentation is up to date. MS Word, as the author suggests, requires someone to keep it up to date; and we all know how well that turns out. There are many tools out there, all with their good points, but I personally wouldn't ever consider one which didn't offer me the ability to run as part of a build, or nightly task.

On the subject of tools; my shameless plug is for DBDocumentor from Pikauba Software (http://www.pikauba.com/DBDoc/details.htm). It works from the SQL sources so it won't be for everyone, but the tradeoff is that you can get information about result sets and data accesses/modifications. It also has support for SSRS RDL, so that's a good bonus.

Cheers

RE: Document Your Database

Stefan K — Thu, 11 Jan 2007 05:06:00 GMT

Hi,

about Tools: I made good experiences with Enterprise Architect from Sparx Systems . It is a software modelling tool. http://www.sparxsystems.com.au/

You can import DDL Schemas by using OLEDB and ODBC connections to many database systems. The tool reads the extended Properties from SQL Server Objects and from others (DB2).

You can edit the DDL with a GUI and export DDL scripts.

Best regards,

Stefan

RE: Document Your Database

shashi kant — Thu, 11 Jan 2007 04:44:00 GMT

Good Thing you have been introduced

it really help in documentation ... related thing........

RE: Document Your Database

Albie van Rensburg — Thu, 11 Jan 2007 03:56:00 GMT

Decent advice. Most design methodologies (especially agile methods) have heavy documentation requirements for a project - not only does it ease position transfer such as job replacements, but also, it really helps to have documentation of stuff you wrote yourself a couple of months/years ago when you need to work on it again. Good naming conventions of columns and variables also go a long way to clearing up understanding of old code/tables.I like the ability to comment on a table column directly. The closer to the actual usage a comment is, the more valuable I rate it.

Document Your Database

Vasant Raj — Fri, 29 Dec 2006 11:16:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/2784.asp