Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Document Your Database Expand / Collapse
Author
Message
Posted Thursday, January 11, 2007 11:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.



ATB

Charles Kincaid

Post #336235
Posted Thursday, January 11, 2007 12:35 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:39 AM
Points: 114, Visits: 612

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.




Post #336247
Posted Friday, January 12, 2007 5:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 18, 2009 8:21 AM
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

Post #336383
Posted Friday, January 12, 2007 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2007 8:42 AM
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.
Post #336467
Posted Friday, January 12, 2007 11:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 2,907, Visits: 1,832
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
Post #336574
Posted Friday, January 12, 2007 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2008 12:45 AM
Points: 3, Visits: 10
This idea seems to be good.
Post #336642
Posted Thursday, January 18, 2007 1:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 18, 2013 4:06 PM
Points: 471, Visits: 589
also sqlspec does more than just SQL Server.

Oracle, MySQL, Access, Analysis Services are supported.

DB2, Sybase, PostgreSQL coming soon...


---------------------------------------
elsasoft.org
Post #337920
Posted Thursday, February 1, 2007 3:40 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:13 PM
Points: 516, Visits: 1,563

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




Post #341830
Posted Friday, February 2, 2007 11:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 2,907, Visits: 1,832
You can also right-click on the object in the object tree within SQL Query Analyser

LinkedIn Profile
Newbie on www.simple-talk.com
Post #342141
Posted Wednesday, November 14, 2007 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:08 PM
Points: 35, Visits: 176
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
Post #422298
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse