During software development, most of the developers tend to use tools such as Navicat, HeidiSQL, etc to connect to databases for doing various activities such as writing SQL, creating views, applying indexes. One of the activities is viewing the table structure which is created during data analysis to know what information they should store in each column of the table. Here I would like to introduce about SQL script which is used to query those table's info ( DATA DICTIONARY ) from the information schema of the databases.​ The script we are going to introduce here is useful for creating a data dictionary especially for business analyzers or database administrators after analyzing the business flow ERD diagram.

Below is the sample format of the data dictionary which is usually used in the development stage. This table information is very vital for communication between developers and analyzers in case there is an updated change while coding. Here is the sample data dictionary of the table: tb_school 

We conduct our experiment based on PostgreSQL database version 12.0 and MySQL 8.0 which are the most popular nowadays. I also provide the script for each of the databases too, which you can freely use. Once again, this script is beneficial for business analysts or/and database administrators/developers.

Here is the same PostgreSQL Script

SELECT DISTINCT CAST
	( DTD_IDENTIFIER AS INTEGER ) AS NO,
	INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,
	NULL AS COLUMN_NAME_KH,
CASE
		
		WHEN UDT_NAME = 'bpchar' THEN
		'char' ELSE UDT_NAME 
	END AS UDT_NAME,
CASE
		
		WHEN UDT_NAME LIKE'%char%' THEN
		CHARACTER_MAXIMUM_LENGTH 
		WHEN UDT_NAME LIKE'%int%' THEN
		NUMERIC_PRECISION 
		WHEN UDT_NAME LIKE'%numeric%' THEN
		NUMERIC_PRECISION 
		WHEN UDT_NAME = 'timestamp' THEN
		DATETIME_PRECISION 
	END AS LENGTH,
	COLUMN_DEFAULT,
CASE
		
		WHEN INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME THEN
		'PK' ELSE'' 
	END AS IS_PRIMARYKEY,
	IS_NULLABLE,
	(
	SELECT
		PG_CATALOG.COL_DESCRIPTION ( C.OID, INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION :: INT ) 
	FROM
		PG_CATALOG.PG_CLASS AS C 
	WHERE
		C.OID = ( SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME :: REGCLASS :: OID ) 
		AND C.RELNAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
	) AS REMARK 
FROM
	INFORMATION_SCHEMA.
	COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLE_CONSTRAINTS.
	TABLE_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME 
WHERE
	INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = 'behr' 
	AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = 'scm_jyp_2012271435' 
	AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'wb_approval_flow' 
	AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY' 
ORDER BY
	NO ASC;

In order to use this script, you have to change the names of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME based on your requirement.

Here is the sample MySQL Script

SELECT DISTINCT INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION AS no,
	INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME as column_name,
	NULL as column_name_kh,
	INFORMATION_SCHEMA.COLUMNS.DATA_TYPE as datatype,
	INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH as length,
	INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT as column_default,
	CASE WHEN
	INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY = 'PRI' THEN 'PK' ELSE 
	INFORMATION_SCHEMA.COLUMNS.COLUMN_KEY END AS is_primarykey,
	INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE AS is_nullable,
	INFORMATION_SCHEMA.COLUMNS.COLUMN_COMMENT AS remark
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = 'dap' 
	AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'tb_profile'
	ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION;

In order to use this script, you have to change the names of TABLE_SCHEMA, and TABLE_NAME based on your requirement.

Finally, I hope these snippet codes to retrieve information of tables in the database can help you to generate data dictionary of your whole system in order to communicate well within your team during software development. For further improvement of the usage, you can also create a function with my provided script so you don't have to copy/paste all over again.