Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Document Your Database


Document Your Database

Author
Message
Charles Kincaid
Charles Kincaid
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: 1119 Visits: 2383

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-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 856

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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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 (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3175
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 (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
This idea seems to be good.
jezemine
jezemine
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 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
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3175
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
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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