How to Find a Specific Column Name (e.g Custom Annotation) in the NetApp OCI DWH

on

The following is quite a nice query if you're trying to find everywhere a particular column name appears in the NetApp OnCommand Insight Data Warehouse MySQL database. Perhaps you are looking for a custom annotation, and want to find the table(s) that annotation presents itself in. The SQL query searches through all column names, in all tables, in all schemas, in the OCI DWH. In the example below we use country as the custom annotation we are searching for (the search is not case sensitive.)

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_COMMENT FROM

(SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_capacity'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_capacity_efficiency'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_cloud_cost'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_custom'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_fs_util'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_inventory'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_performance'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_ports'

UNION SELECT * FROM information_schema.columns WHERE table_schema = 'dwh_reports') as t0

WHERE COLUMN_NAME = 'Country'; -- for example...


Image: Search all Columns in all Tables in all Schemas (MySQL)


NetApp DWH SQL: Find Latest Updated/Created Tables in dwh_custom and test

Along similar lines, something else that is useful if you've inherited a DWH SQL instance with many tables in dwh_custom or test that you're not sure what they're for or if they are even used any more.

select TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA = 'dwh_custom' order by UPDATE_TIME desc;

select TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA = 'dwh_custom' order by CREATE_TIME desc;

select TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA = 'test' order by UPDATE_TIME desc;

select TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,UPDATE_TIME,TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA = 'test' order by CREATE_TIME desc;


Comments

Post a Comment