Do you generate your data dictionary? If so, how?
I use extended procedures in SQL Server 2005 to hold onto table and field information. I have some queries that create a dictionary out of them, but it's ... meh. Do you have a particular query or tool you use? Do you generate it off of your database diagrams?
Googling for "data dictionary sql server" comes up with many queries, but they're all about equally as attractive. Which is to say, good starting off points, but not production ready.
I generate it from INFORMATION_SCHEMA views plus other tables of metadata which are application specific.
I also use wildcard searches of INFORMATION_SCHEMA.ROUTINES in order to track usage patterns within code and identify unused columns and tables.
This article just showed up in one of my news feeds: http://www.mssqltips.com/tip.asp?tip=1619
We generate the database's dictionnary on the developper's side of the application. We have a nice procedure using an ADODB connection + ADOX objects and collections. This procedure will browse all tables in the database. The following main data is collected:
You can also follow up field default values, etc.
It is then possible for example to :
currency_id (primary key of the
Tbl_currency table)is referenced,
and if referential integrity is each
time properly implemented (we very
often create the field without
implementing the corresponding rules
...).description"
fields) are of similar data
type/size. Nothing is so frustrating
that having a item_Description nvarchar(50) field in a table, and a
document_Description ntext in another
table!All the data extracted through the procedure is inserted in a local table (could be an XML file or whatever) and saved for further use.
A column dictionnary/report can be generated from this data through
SELECT DISTINT columnName FROM Tbl_Column
Personally I prefer to create the Data Dictionary while the database is being designed. Of course, this isn't always an option...
I think the answer depends on the current state of the database? Is it done and in production? Have you yet to start on it? (etc.)
In the past, like Cade Roux, I've pulled the information from INFORMATION_SCHEMA in to an access database. Currently we have developers occasionally adding information about the different tables, columns, stored procedures, functions, etc. to the Access database. Inside the Access database, we've created reports to output a neat looking "Data Dictionary" print out.
It is not the most efficient way to accomplish creating a data dictionary but given that the project had gone 3 years without any sign of a data dictionary, this is what we had to do.
So ultimately, the answer to this depends on your database's state.
Best Regards,
Frank
SchemaSpy is a really nice tool that can reverse engineer a description of a database. This includes:
We use extended properties.
To read them, we use sys.extended_properties It makes things a lot easier.
We also use Red Gate SQL Doc
We wrote our own Data dictionary utility that used extended properties, but when we found Redgate's tool, we abandoned it for their tool. Worked great for us! I guess it helped that we already had field and table descriptions out there in extended properties. Not to advertise for a company, but they have a 14day free trial. It's worth a look. http://www.red-gate.com/products/SQL_Doc/index.htm
I use this tool (open source): http://www.codeplex.com/datadictionary. All the information I create gets added in the Extended Properties of the database.