The Attivo Blog

If you are dissatisfied with the performance and look of the native Indented BOM Report, Attivo has available an external report that will resolve the look and performance issues. The report uses a combination of SQL stored procedures and Crystal Reports. For more information , call us at 949-253-9639 or contact us.

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