Are you creating a data dictionary?

Question

Are you creating a data dictionary? If so, how?

I use advanced procedures in SQL Server 2005 to store information about tables and fields. I have some queries that create a dictionary of them, but this is ... meh. Do you have a specific query or tool that you are using? Do you generate it from database charts?

Googling for the "SQL SQL Query Dictionary" contains many queries, but they are all equally attractive. That is, good starting points, but not the readiness of production.

Answer 1

SchemaSpy is a really good tool that can reverse engineer a database description. It includes:

• ERD
• List of tables, columns, and constraints
• Warning about database anonymity (for example, tables without indexes)

Answer 2

Recently, I had the task of documenting a fairly large database (about 500 objects), and the details that I found here really helped.

Here are some reviews on how we implemented this - I hope someone finds this useful, although its quite late.

Technics:

• Advanced properties in all objects + a third-party tool for creating documents

What has been documented:

• All tables and some columns (we added good descriptions for all tables to really make sure that it is clear what the table is about)

• All views are descriptions of why the view was created, which tables are included in the data, etc., and also when to use

• All stored procedures - when viewing the process, we found that we had many repeated stored procedures (the developers did not bother to see if proc exists to create new ones).

• All UDFs and some other objects, but not all (we really had no need to document triggers)

What we have finished is to force our database administrator to reject all DDL updates that come from developers if there are no advanced properties.

We also have the planned task of automatically re-creating documentation every two weeks.

Answer 3

We use advanced properties.

To read them, we use sys.extended_properties. This makes things a lot easier.

We also use Red Docs SQL Doc

Answer 4

I am using this tool (open source): http://www.codeplex.com/datadictionary . All the information I create is added to the advanced properties of the database.

Answer 5

I generate it from the INFORMATION_SCHEMA views plus other metadata tables that are application specific.

I also use INFORMATION_SCHEMA.ROUTINES wildcards to track usage patterns in code and identify unused columns and tables.

This article appeared in only one of my news feeds: http://www.mssqltips.com/tip.asp?tip=1619

Answer 6

We generate database dictionaries on the application development side. We have a good procedure using the ADODB connection + ADOX objects and collections. This procedure will look at all the tables in the database. The following master data is collected:

• Tablename
• Columnname
• Columntype
• ColumnSize
• bool_ColumnIsThePrimaryKey
• bool_ColumnHasReferentialIntegrityConstraint

You can also track default field values, etc.

Then you can, for example:

• check how many tables my currency_id field ( Tbl_currency primary key) is referencing, and if the referential integrity is correctly executed time (we very often create a field without following the appropriate rules ...).
• Make sure that fields of similar logical type (for example, " description " fields) have the same Type / Size data. Nothing upsets that with the field item_Description nvarchar(50) in the table and document_Description ntext in another table!
• and etc.

All data extracted from the procedure is inserted into the local table (maybe an XML file or something else) and saved for future use.

Using this data through

can create a dictionnary / report column,

 SELECT DISTINT columnName FROM Tbl_Column 

Answer 7

Personally, I prefer to create a data dictionary during database development. Of course, this is not always an option ...

I think the answer depends on the current state of the database? Is this done in production? You have not started on it? (Etc.).

In the past, like Cade Roux, I pulled information from Information_SCHEMA into an access database. Currently, developers sometimes add information about various tables, columns, stored procedures, functions, etc. To the Access database. Inside the Access database, we created reports to display a clear view of the “Data Dictionary”.

This is not the most effective way to create a data dictionary, but provided that the project has passed 3 years without any signs of a data dictionary, this is what we had to do.

So, ultimately, the answer to this depends on the state of your database.

Best wishes,
Franc

Answer 8

We wrote our own data dictionary utility that used advanced properties, but when we found the Redgate tool, we abandoned it for our tool. Worked great for us! I think it helped us already have descriptions of fields and tables in advanced properties. Do not advertise the company, but they have a 14-day free trial. It is worth a look. http://www.red-gate.com/products/SQL_Doc/index.htm

Answer 9

I got lucky with the SQL Data Dictionary .