DDLtoExcel

Creates an Excel workbook based on SQL DDL commands.

Before You Proceed

Background

I developed this system because we're working on a system with a large database and we don't have any documentation for it. The database used Oracle. It has multiple connected schemas - each schema has hundreds of tables and each table contains several columns. In order to understand how each table are connected to one another, I extracted the DDL commands via sqldeveloper. Then process each line of code to create an Excel workbook.

This helped me with two things:

1. Understanding the database structure.
2. Creating a documentation via an Excel file.

Limitations

• It works only on Oracle database.
• It has limited DDL command support (I only added those I needed at the moment).
• Each Table Sheet is named after the TABLE name, it has to be limited to 31 characters, or else it will throw an error.
  • If the SCHEMA name is INCLUDED in the DDL commands, the name of Table Sheet will be "SCH[n].TableName" where n is the number of schema.
  • I decided not to include the SCHEMA name in the name of Table Sheet because of the 31-character limitation of Excel.
• For multiple connected SCHEMA
  • If the SCHEMA name is NOT INCLUDED in the DDL commands, each TABLE MUST have a UNIQUE name, or else it will throw an error due to duplicate sheet names.

Getting Started

These instructions will get you a copy of the project up and running on your local machine for development purposes.

Prerequisites

• .NET Framework 4.7.2
• Microsoft Excel 2013
• Microsoft Visual Studio 2015

NOTE:

The above versions are what I used in developing the project.

• For .NET Framework, you can download the version I used or just change the settings on the project's Properties.
• For Microsoft Excel, you can change the References used in the project.
• For Microsoft Visual Studio, it needs to have the .NET desktop development workload installed.

Opening and Executing the project

1. Clone this repository on your local machine.
2. Locate the project on your local machine, open "DDLToExcel.sln" via Microsoft Visual Studio.
3. Build the project, then Start.

How It Works

The system creates the table structures based on the DDL commands on a .sql file.

Supported Commands

• CREATE TABLE
• CREATE GLOBAL TEMPORARY TABLE
• ALTER TABLE
• COMMENT ON TABLE
• COMMENT ON COLUMN

It then creates an Excel workbook with:

Summary Sheet

• It contains the list of TABLE and their corresponding COMMENT.
• Each TABLE has a hyperlink to its corresponding Table Sheet.

Example:

No.	Table Name	Comment
1	Table1 Name	Table1 Comment
2	Table2 Name	Table2 Comment
3	Table3 Name	Table3 Comment
...	...	...
n	Table[n] Name	Table[n] Comment

Table Sheet

• Each TABLE has it's own Table Sheet.
• It contains the list of COLUMN and their corresponding CONSTRAINT and COMMENT.
• Each FOREIGN KEY constraint has a hyperlink to its corresponding Table Sheet.

Example:

No.	Column Name	Data Type	Default	Constraint					Comment
				Not Null	Primary Key	Unique	Foreign Key	Check
1	Column1 Name	Column1 Data Type	[AUTO INCREMENT] | [DEFAULT VALUE]	[YES]	[YES]	[YES]	[Table Name].[Column Name]	[CONDITION]	Column1 Comment
2	Column2 Name	Column2 Data Type	[AUTO INCREMENT] | [DEFAULT VALUE]	[YES]	[YES]	[YES]	[Table Name].[Column Name]	[CONDITION]	Column2 Comment
3	Column3 Name	Column3 Data Type	[AUTO INCREMENT] | [DEFAULT VALUE]	[YES]	[YES]	[YES]	[Table Name].[Column Name]	[CONDITION]	Column3 Comment
...	...	...	...	...	...	...	...	...	...
n	Column[n] Name	Column[n] Data Type	[AUTO INCREMENT] | [DEFAULT VALUE]	[YES]	[YES]	[YES]	[Table Name].[Column Name]	[CONDITION]	Column[n] Comment

Acknowledgements

• Icons made by Freepik from www.flaticon.com