The Attivo Blog

Ever need to find all the places where a data element might appear in a database?  You might need to make a change to the format of the column, or you just need to know where it is used in order to make a global update.  This can be very time consuming if you try to do it the hard way.  Here's a few easy to use scripts for you to run instead, using Macola Progression SQL and Macola ES (ERP systems) databases as  examples: I. Macola Progression SQL database: a. Example: Find all tables in the data base that contain the data element "Prod_Cat" select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from dbname.information_schema.columns where column_name = 'prod_cat' order by table_name b. Example: Find all tables in the data base that contain a data element with the value of "Prod" somewhere in the element name. select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from dbname.information_schema.columns where column_name like '%prod %' order by table_name II. Macola ES Data base a. Example: Find columns with a specific name select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from information_schema.columns where column_name = 'prod_cat' order by table_name b. Example: Find all tables in the data base that contain a data element with the value of "Prod" somewhere in the element name. select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE,COLUMN_DEFAULT from information_schema.columns where column_name like '%prod%' order by table_name c. Example: Find columns from a specific table select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from information_schema.columns where table_name = 'cicmpy' order by table_name