Documenting a SQL Server 2000 Database

I am looking for an easy way to document the specifications of my tables, views, etc. In MS Access, one could use the "Documenter" utility to print out field names, data types, field sizes, and comments. Does SQL Server 2000 have a similar utility?

If not...
1. Why not? This would seem to be very useful.
2. What is the recommended way to document database specifications?

My ultimate goal is to develop an administration manual for our database.

Please note: I looked at the database designer diagram utility which is great for visually representing the relationships between tables. However, I am looking for a utility that will print out the field names, data types, field sizes and comments related to the fields.

Any help would be appreciated.

Thanks :)I've used the INFORMATION_SCHEMA views, such as COLUMNS to get at this type of information.

This thread might help:view post 353693

Also, to get at the comments, you will likely need to use the system function fn_listextendedproperty.

Terri|||The other thing I forgot to mention was the system stored proceduresp_help.

Terri|||tmorton,

Your suggestions are perfect! Both the sp_help and fn_listextendedproperty provide the information I am looking for.

Follow-up question...

If I want to obtain the column information from both of these procedures for a particular table, how would I structure the query such that there would be a single result set? For example, a result set that would have column name, data type, and length from sp_help, and description from fn_listextendedproperty?

I apologize if my question seems rudimentary. I have limited experience with combining stored procedures and functions.

Thanks for your help :)|||I finally figured out how to obtain what I need.

Below you will see the stored procedure that I now use to obtain the following information: 1. Table name, 2. Column name, 3. Column position, 4. Allowance of nulls, 5. Maximum length, and 6. Field description. I used INFORMATION_SCHEMA and fn_listextendedproperty. The stored procedure requires that the table name be specified (i.e., @.TableName parameter).

Here it goes...

CREATE PROCEDURE A_GetTableSpecs_Desc
(
@.TableNamevarchar(100)
)
AS
SELECT
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
ORDINAL_POSITION AS Position,
IS_NULLABLE AS AllowNulls,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
value AS FieldDescription
FROM INFORMATION_SCHEMA.COLUMNS AS MyColumns
LEFT JOIN
(
SELECT objname, name, value
FROM ::fn_listextendedproperty
(
NULL,
'user',
'dbo',
'table',
@.TableName,
'column',
default
)
) AS MyExtendedProperty
ON MyColumns.COLUMN_NAME = MyExtendedProperty.objname
WHERE MyColumns.TABLE_NAME = @.TableName
ORDER BY Position
RETURN
GO

Any better suggestions would be much appreciated. Thanks :)