How can I document tables and fields in SQL Server 2005

Question

I've missed this somehow in my SQL Server learning process.

I'd like to be able to document tables/fields/procedures in our SQL Server 2005 environment, and I can't find a way to do this. Is this possible, or would I need a third party app of some kind?

This sounds like a no-brainer feature, so I'm probably overlooking it.

Thanks!

JH

EDIT: Specifially, I'm talking about documenting SQL objects ('this table is used for blah, and is referenced by foo, and bar.'). Thanks for all the help - you guys nailed it.

Answer 1

One of the best documenting tools is called SQL Doc by RedGate software.

Answer 2

SQL Server has Extended Properties that you can use for this purpose.

Answer 3

For tables there is a Descrition property for each field you can use to make comments. You can also make comments in your Stored Procedures.

sp_help object

If you want to know the table structure, indexes and constraints of a table.

sp_tables tablename

Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.

sp_stored_procedures storedprocname

Returns a list of stored procedures in the current environment.

Answer 4

There are plenty of tools - I personally really like SQL Doc (by Red-Gate; mentioned before), but also ApexSQL's ApexSQL Doc.

But a lot of other tools also will read and document database schemas - if you already have a help writer, check it out - it might even support database documentation, too!

Marc

Answer 5

With stored_procedures, views, and functions you can simply add a comment syntax to the object definition like:

/*
some comment
*/
create view dbo.view sample ...

with tables and fields it is a bit more complicated, you can use Extended Properties as suggested by Chris. You can access it through Management Studio by selecting the table in object explored and Properties from context menu.

If you can afford investing in Tools you can pick SQL Doc as suggested by TheTXI