SQL to create data dictionary reports

Tuesday 8 December 2020 @ 10:13 pm

I was recently trying to create a report and was having trouble finding a specific table. I knew the field name, but it wasn’t in any of the tables where I expected it to exist. Lets just say the table names in this database are cryptic.

Fortunately, most of the mainstream databases allow you to query the system tables to list all the tables and fields. I found some great SQL online and created a report to read the table structures and search for the field I needed. The query allowed me to create a searchable data dictionary report, or schema, for this database.

The SQL example I found is on a site that lists similar SQL for other databases as well:

This example is for MS SQL Server:

This example is for Oracle:

This example is for MySQL:

So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.

Leave a Reply

Recrystallize Pro

Crystal Reports Server