This generates outdated documentation when the database were updated, and those documents became easily unusable after some months.
We started using the «Description» field available in Microsoft SQL Server to document our Tables and every Fields.
Once it is done, we created 2 very simple procedures to extract the description content of those fields, which are stored in «extended properties» system tables.
I provide you those 2 scripts, 1 to list the tables and descriptions, the other for the fields description.
I hope it may help you.
*** This script works fine in SQL 2005, you may have to modify it a little for SQL 2000 ***
********** Get tables description **********
create PROCEDURE spu_getdescriptionTables
AS
BEGIN
SELECT
t.name as tablename,
isnull(exprop.[value], ») as Descript,
t.id as table_id
from
SYSOBJECTS t
left join sys.extended_properties exprop
ON exprop.major_id = t.id
AND exprop.minor_id = 0
where
t.name <> ‘sysdiagrams’ and t.xtype = ‘U’
order by
tablename
END
********** Get fields description **********
ALTER PROCEDURE [dbo].[spu_getdescriptionFields]
@tableid numeric(18,0) = 0,
@tablename varchar(50) = »
AS
BEGIN
if @tablename <> »
select @tableid = t.id from sysobjects t where name = @tablename
SELECT
t.name as tablename,
c.name as fieldname,
case isnull(collation_name, »)
when » then types.name + ‘(‘ + cast(c.precision as varchar) + ‘,’ + cast(c.scale as varchar) + ‘)’
else types.name + ‘(‘ + cast(max_length as varchar) + ‘)’
end as datatype,
— types.name + ‘(‘ + cast(c.max_length as varchar) + ‘)’ as datatype2,
case c.is_nullable
when 0 then »
else ‘X’
end
as Nullable,
case c.is_identity
when 0 then »
else ‘X’
end
as Identite,
isnull(defaultvalues.text,») as Defaut,
isnull(exprop.[value], ») as Descript
from
sys.columns c
INNER JOIN SYSOBJECTS t
ON t.id = c.object_id and t.name <> ‘sysdiagrams’ and t.xtype = ‘U’
INNER JOIN systypes types
ON c.system_type_id = types.xtype and types.name <> ‘sysname’
LEFT JOIN syscomments defaultvalues
ON c.default_object_id = defaultvalues.id
left join sys.extended_properties exprop
ON exprop.major_id = c.object_id
AND exprop.minor_id = c.column_id
where
@tableid = 0 OR t.id = @tableid
order by
tablename,
c.column_id
END