SQL Database documentation?

Question

specifically, I use SQL Server 2005, but the solution might be brand independent.

Need: You have 40 database tables, in some, you may have a column which is a integer values 1-9 representing some enum. Sure it makes sense in code, but not if you need to SQL the DB in a report getting only rows of type "active" but you have no clue if active is 1,2 or 3...

my solution: I have a wiki page and I tell each developer that each table must be there, with the schema and for each column the possible values with explanation. I also generate the SQL server 2005 database diagram, but that is only marginally helpful since you have so many tables and so many lines connecting them.

Is there a better way do document what a table is, what a column is and publishing it?

---

Note regarding answers: Please don't answer this question thinking like an engineer! Answers like "if you have an extra table linked with a foreign key, then anyone can see the values" misses the point. A solution is good if:

1. I can print it and send it to a partner
2. I can write free text, documenting the datable/clm. Not everything in life is self documenting. I may want to say "this table is a report supporting table and is de-normalized data from XYZ tables.

Answer 1

In the case of this enum you're describing, you could simply set the column up as a foreign key to a table that defines the enum values. Then your database is self-documenting; and nobody is forcing you to join the table if your code knows what values it needs.

Answer 2

Extended properties is where my team stores DB documentation. We then have a set of tools that extract the data and writes it to a wiki page for centralised documentation.

Answer 3

If the column name is "StatusID", and there is a "Status" table, with "StatusID" and "Name" fields, I think it should be painfully obvious.

Also, buy a bigger printer. Nothing like a 36" printer to get that poster-sized schema on the wall.

Also, read up on extended properties. They seem to be what you want -- although, as stated, it should not be needed.

Start here: http://msdn.microsoft.com/en-us/library/ms190243.aspx

Answer 4

There is a tool call Data Dictionary Creator that you can use to have your documentation in sync with your tables. You can use Red-gate tools. I use the first.

It generate a HTML report that you can print.

Answer 5

Please don't answer this question thinking like an engineer!

If anyone other than engineer-types are looking at your database, you've got problems.

I can print it and send it to a partner
I can write free text...

If you're setting requirements of 1. printing, and 2. sharing... then go with a wiki or a word document.

Sorry nobody has your magic answer. Databases just don't lend themselves to documentation beyond their self-documenting features (the extended properties that Stu mentioned).

I would also suggest you avoid Magic Numbers for exactly the reason you describe: Nobody knows what the heck they mean! Instead, use a foreign key, like I already recommended, so that the data is self-documenting.

Answer 6

Does your shop have some kind of data modeling tool that the developers can use? Most of these tools have good documentation features built in, so it becomes a simple two click job after generation or updates to refresh the documentation.

I currently use Toad Data Modeler (I've been using their beta cycles for a while now: free and little to no bugs) and they have a great HTML documentation tool that provides everything, including ER diagrams.

Answer 7

Try dbdesc. It has a built-in extended properties editor that allows you to annotate any database object easily (tables, fields, stored procedures, parameters, etc).

It generates documentation in various formats that are easy to share: PDF, Word, HTML, etc. This tool has the best ready-to-print format by far.

Answer 8

I have found schemaSpy to be quite handy on documenting (and understanding) a database schema. It connects the DB and produces documentation by analyzing the metadata. It even produces ER diagrams and you can feed it with XML file containing the comments for tables and columns.