Document Your Database

Question

Post reply

Document Your Database

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Kiran-396897 Valued Member Points: 69 More actions · Answer 1

Kiran-396897

Valued Member

Points: 69

January 12, 2007 at 3:18 pm

#683070

This idea seems to be good.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

also sqlspec does more than just SQL Server.

Oracle, MySQL, Access, Analysis Services are supported.

DB2, Sybase, PostgreSQL coming soon...

---------------------------------------
elsasoft.org

steve block SSCertifiable Points: 7698 More actions · Answer 3

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 SSC Guru Points: 76036 More actions · Answer 4

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

LinkedIn Profile

Kris-420132 Old Hand Points: 399 More actions · Answer 5

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

steve block SSCertifiable Points: 7698 More actions · Answer 6

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

Steve

Kris-420132 Old Hand Points: 399 More actions · Answer 7

I'm using sql 2005

Do you by chance know how to access those objects in 2005?

steve block SSCertifiable Points: 7698 More actions · Answer 8

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

Steve

JAG-360157 Valued Member Points: 56 More actions · Answer 9

Hi Kris,

this is the same query for SQL 2005:

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 join

sys.objects SO on SC.object_id = SO.object_id inner join

sys.types ST on ST.user_type_id = SC.user_type_id left join

sys.extended_properties sp on sp.major_id = so.object_id and sp.minor_id = SC.column_id

and sp.name = 'MS_Description'

where SO.type = 'U'

order by SO.name, SC.name

Regarding your second question:

if i wanted to browse these tables, how would I access them?

Just go to DB -> Views -> System views

Hope it helps.

Regards,

Jose

Kris-420132 Old Hand Points: 399 More actions · Answer 10

Many thanks Jose

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

thanks again

Kris

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 11

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

brhunter-540485 SSC Enthusiast Points: 146 More actions · Answer 12

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 13

brhunter (1/2/2008)
Great advice. However, I think a better article would be on the practice of creating "Self Documenting" database Schemas.

See Joe Celko's book "SQL Programming Style" ISBN:0-12-088797-5

ATBCharles Kincaid

SSIS Guy SSCommitted Points: 1667 More actions · Answer 14

Here is one more Free Tool to document Sql Server

SSIS Guy SSCommitted Points: 1667 More actions · Answer 15

Here is one more Free Tool to document Sql Server