Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
ShawkiHi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki