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:
https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

This example is for Oracle:
https://dataedo.com/kb/query/oracle/list-table-columns-in-database

This example is for MySQL:
https://dataedo.com/kb/query/mysql/list-table-columns-in-database

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

Jeff-Net
Recrystallize Pro

Crystal Reports Server