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