data_dictionary_scripts.md

Multiple Platform Data Dictionary Scripts

What is a Data Dictionary?

A "Data Dictionary" is simply a list of tables and views, their column properties such as name, ordinal position, data type/length/size/precision, allows nulls, and of course the column description. A good data dictionary also indicates which columns are a part of a primary key, foreign key, unique key, or distribution key (Greenplum).

Data dictionaries can be used to train new employees how your system is setup. They can be used as a baseline for new projects, an "as-built" set of documentation from which to start. They can be used as close-out documentation to wrap-up a project and push it out into Excel on a wiki or Sharepoint, etc.

Overview

Sure, there are many tools out there which will automatically build a data dictionary for you from an existing schema. Some of those tools include Visual Studio, Toad, Oracle SQL Developer, etc. However, sometimes you just want to run a script to fetch the data dictionary. Perhaps you want to modify the script to log the data dictionary out nightly to a database table or to files to watch and notify on changes. Maybe you just want to pull a large schema and dump it into Excel where you can foramt it to look pretty then use filter to quickly sift through hundreds of tables and thousands of columns. Regardless of your need, the following multi-plaform scripts should have you covered.


The following sections walk you thru using the scripts. At the bottom of the article is a grid with all the available database platforms, their matchings scripts and YouTube tutorials if you'd prefer just watching how to use them.

Using the Script

STEP 1. Download Script

In the table at the bottom of this article, find your preferred database platform and click the appropriate "Script" link. This brings up the raw text file in the browser, minus any special HTML formatting so that you can simply copy the script then paste it into a new text file or directly to a SQL editor. Alternatively you could clone the repository to your local workstation (pull everything using Git for source control), or click the green Code button and download the repository as a zip file.

STEP 2. Run Script

First, open your SQL Editor (MySQL Workbench, Toad, etc.).
Next, open the SQL script as shown below and follow the numbered steps (blue dots in screenshot) to configure and run the script:

1. Schema/Database Name: Select the schema or database name; "sakila" in the example.
2. Tables Only? Set the "v_TablesOnly" var to either "YES" for tables only, or "NO" (default) to also include views
3. Execute Click the appropriate button in your IDE to run the script

STEP 3. View Results & Export

Once the SQL script is done executing, you should have hundreds or perhaps even thousands of rows of Data Dictionary metadata depending on the size of your schema. Go ahead and export this to either a CSV file or directly into Microsoft Excel. From there you can format the data dictionary to make it look pretty and add filters to make it quickly searchable. See the YouTube tutorials below for more details.

Related Content

• Free tool does same as script: Github Link

Scripts & Tutorials by Platform

Links to the script source code as well as video tutorials are listed below, by platform (MSSQL, Oracle, MySQL, etc.):

	Greenplum Greenplum "Data Dictionary" Script Greenplum "Data Dictionary" Tutorial
	MS SQL Server MSSQL "Data Dictionary" Script MSSQL "Data Dictionary" Tutorial Note: MSSQL 2017 forward only (STRING_AGG fx)
	MySQL MySQL "Data Dictionary" Script MySQL "Data Dictionary" Tutorial
	Oracle Oracle "Data Dictionary" Script Oracle "Data Dictionary" Tutorial
	PostgreSQL PostgreSQL "Data Dictionary" Script PostgreSQL "Data Dictionary" Tutorial

If you like these scripts, be sure to click the "Star" button above in GitHub.

Also, be sure to visit or subscribe to our YouTube channel www.DataResearchLabs.com!