SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Document Your Database


Document Your Database

Author
Message
Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2175 Visits: 2384

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.



ATBCharles Kincaid
Terrie
Terrie
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 885

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.





Robert Bedick
Robert Bedick
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 68
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


JAG-360157
JAG-360157
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 2
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.
David.Poole
David.Poole
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10404 Visits: 3341
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.

LinkedIn Profile

Newbie on www.simple-talk.com
Kiran-396897
Kiran-396897
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 10
This idea seems to be good.
jezemine
jezemine
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 589
also sqlspec does more than just SQL Server.

Oracle, MySQL, Access, Analysis Services are supported.

DB2, Sybase, PostgreSQL coming soon...

---------------------------------------
elsasoft.org
steve block
steve block
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1046 Visits: 1563

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





David.Poole
David.Poole
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10404 Visits: 3341
You can also right-click on the object in the object tree within SQL Query Analyser

LinkedIn Profile

Newbie on www.simple-talk.com
Kris-420132
Kris-420132
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 179
Andy DBA (1/11/2007)


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

--List tables, columns and column descriptions
select SO.name as 'table', SC.name as field,
ST.name as datatype, SC.length as 'size',
sp.value 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'
where SO.xtype = 'U' and SO.status > 0
order by SO.name, SC.name


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

Thanks in advance
Kris

However the only table in "System Tables" is sysdiagrams
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search