How to Visualize Your App’s Google Play Store Metrics in Looker Studio
Android App Development
As a digital agency focused on app development, we often find ourselves inheriting projects that other developers started from which we need to first analyze and understand the schema of the existing database.
This requires us to be able to look at an existing database and extract from within the metadata on the structure of the database, from which we then model it using a tool like Lucidchart.
Exporting data from a relational database like SQL Server or Postgres is a relatively straightforward process, as any database management tool worthy of its own title has some sort of “save results to CSV” type of option.
But what if the data you want to export is not the data in the database, but rather the table structure of that database?
Below, I’ve put together and shared some very basic queries you can run on each of the major RDBMS systems out there to output the schema of a database, including column names, data types, and foreign key constraints in a tabular format that can be easily saved to a CSV.
SQL Server
SELECT t.TABLE_CATALOG,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,n.CONSTRAINT_TYPE,k2.TABLE_SCHEMA,k2.TABLE_NAME,k2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_CATALOG=c.TABLE_CATALOG
AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN(INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n
ON k.CONSTRAINT_CATALOG=n.CONSTRAINT_CATALOG
AND k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA
AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
ON k.CONSTRAINT_CATALOG=r.CONSTRAINT_CATALOG
AND k.CONSTRAINT_SCHEMA=r.CONSTRAINT_SCHEMA
AND k.CONSTRAINT_NAME=r.CONSTRAINT_NAME)ON c.TABLE_CATALOG=k.TABLE_CATALOG
AND c.TABLE_SCHEMA=k.TABLE_SCHEMA
AND c.TABLE_NAME=k.TABLE_NAME
AND c.COLUMN_NAME=k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k2
ON k.ORDINAL_POSITION=k2.ORDINAL_POSITION
AND r.UNIQUE_CONSTRAINT_CATALOG=k2.CONSTRAINT_CATALOG
AND r.UNIQUE_CONSTRAINT_SCHEMA=k2.CONSTRAINT_SCHEMA
AND r.UNIQUE_CONSTRAINT_NAME=k2.CONSTRAINT_NAME
WHERE t.TABLE_TYPE='BASE TABLE';
Postgres
SET enable_nestloop=0;SELECT t.table_catalog,t.table_schema,t.table_name,c.column_name,c.ordinal_position,c.data_type,c.character_maximum_length,n.constraint_type,k2.table_schema,k2.table_name,k2.column_name
FROM information_schema.tables t NATURAL
LEFT JOIN information_schema.columns c LEFT JOIN(information_schema.key_column_usage k NATURAL
JOIN information_schema.table_constraints n NATURAL
LEFT JOIN information_schema.referential_constraints r)ON c.table_catalog=k.table_catalog
AND c.table_schema=k.table_schema
AND c.table_name=k.table_name
AND c.column_name=k.column_name
LEFT JOIN information_schema.key_column_usage k2
ON k.position_in_unique_constraint=k2.ordinal_position
AND r.unique_constraint_catalog=k2.constraint_catalog
AND r.unique_constraint_schema=k2.constraint_schema
AND r.unique_constraint_name=k2.constraint_name
WHERE t.TABLE_TYPE='BASE TABLE'
AND t.table_schema NOT IN('information_schema','pg_catalog');
MySQL
SELECT t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,n.CONSTRAINT_TYPE,k.REFERENCED_TABLE_SCHEMA,k.REFERENCED_TABLE_NAME,k.REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA=k.TABLE_SCHEMA
AND c.TABLE_NAME=k.TABLE_NAME
AND c.COLUMN_NAME=k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n
ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA
AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME
AND k.TABLE_SCHEMA=n.TABLE_SCHEMA
AND k.TABLE_NAME=n.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE'
AND t.TABLE_SCHEMA NOT IN('INFORMATION_SCHEMA','mysql','performance_schema');
Oracle
SELECT ORA_DATABASE_NAME,t.OWNER,t.TABLE_NAME,c.COLUMN_NAME,c.COLUMN_ID,c.DATA_TYPE,c.DATA_LENGTH,n.CONSTRAINT_TYPE,r.OWNER,r.TABLE_NAME,r.COLUMN_NAME
FROM ALL_TABLES t
LEFT JOIN ALL_TAB_COLS c
ON t.OWNER=c.OWNER
AND t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN ALL_CONS_COLUMNS nc
ON c.OWNER=nc.OWNER
AND c.TABLE_NAME=nc.TABLE_NAME
AND c.COLUMN_NAME=nc.COLUMN_NAME
LEFT JOIN ALL_CONSTRAINTS n
ON nc.OWNER=n.OWNER
AND nc.CONSTRAINT_NAME=n.CONSTRAINT_NAME
AND n.CONSTRAINT_TYPE IN('P','U','R')LEFT
JOIN ALL_CONS_COLUMNS r
ON n.R_OWNER=r.OWNER
AND n.R_CONSTRAINT_NAME=r.CONSTRAINT_NAME
AND nc.POSITION=r.POSITION
WHERE c.COLUMN_NAME IS NOT NULL;
With any of the above queries you can then simply copy the output in tabular format and paste it into Excel.
After that it is a matter of clicking “Save As” and selecting “CSV” as the file format to have the table schema saved into a CSV file.
Bobby Gill
