Tony Stacey

Looking for something?  Let's say you're looking for a specific data value in a database, but you don't know which table or data elements contain the value.  The SQL procedure below will scan all character string data elements in the database for the specified value.    I. Create the procedure in the required database.  SQL Script below: CREATE PROC SearchAllTables  (@Search char(100) ) AS BEGIN                    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue char(3630))                   SET NOCOUNT ON DECLARE @Table char(256), @Column char(128), @XSearch char(110)                   SET  @Table = ''                   SET @XSearch = QUOTENAME('%' + @Search + '%','''')                   WHILE @Table IS NOT NULL                   BEGIN                              SET @Column = ''                              SET @Table =                              (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' +                              QUOTENAME(TABLE_NAME))                              FROM  INFORMATION_SCHEMA.TABLES                              WHERE TABLE_TYPE = 'BASE TABLE'                              AND QUOTENAME(TABLE_SCHEMA) + '.' +                              QUOTENAME(TABLE_NAME) > @Table                              AND OBJECTPROPERTY(OBJECT_ID                              (QUOTENAME(TABLE_SCHEMA) + '.' +  QUOTENAME (TABLE_NAME)), 'IsMSShipped') = 0)                 WHILE (@Table IS NOT NULL) AND (@Column IS NOT NULL)                 BEGIN                             SET @Column =                            (SELECT MIN(QUOTENAME(COLUMN_NAME))                            FROM  INFORMATION_SCHEMA.COLUMNS                            WHERE TABLE_SCHEMA= PARSENAME(@Table, 2)                            AND TABLE_NAME = PARSENAME(@Table, 1)                            AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')                            AND QUOTENAME(COLUMN_NAME) > @Column)                            IF @Column IS NOT NULL                            BEGIN                                        INSERT INTO #Results                                        EXEC ('SELECT ''' + @Table+ '.' + @Column + ''',                                                    LEFT(' + @Column + ', 3630)                                                    FROM ' + @Table+ ' (NOLOCK) ' +                                                    ' WHERE ' + @Column + ' LIKE ' + @XSearch)                                                   END                                       END                            END             SELECT ColumnName, ColumnValue FROM #Results END   II. To search all columns of all tables in database for the keyword "Computer" EXEC SearchAllTables 'value' This may be a long running process.  The amount of time to execute is dependent on the size of the database you are scanning.

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

Run Macola Lean and Mean

Macola Progression SQL users that have been using Macola for a long time and have not regularly purged the database experience significantly slower processing and much longer times for report execution, invoice posting, GL subsidiary ledger posting, etc. The Attivo Group has developed a data retention utility for Progression SQL that allows the user to first archive all historical data into a separate database, and then purge their system using data retention guidelines.  In this way, NO data is lost. Using SQL tools, the program removes all data that falls outside of the date retention period.  In this way,  historical reporting and analysis can still be performed as required against the archived data, leaving the production database "lean and mean". The data retention process is rules-driven to allow a company to define which files (tables) need to be archived and how much historical data is retained in the production environment. For example, 2 years of accounts receivables invoices and payments may be sufficient for most queries. However, your company may need to keep 5 years worth of receivables information to support some contractual or legal requirements. Using the Attivo data retention utility, the company can have both. 

Designed for ongoing use

The system is designed for ongoing use, as well.  Once you define the data retention schedule, the utility will add any data records in the production company that do not already exist in the archive company, and then purge the production company of any transactions beyond the data retention period. This provides the ability for the company to perform historical trends and analysis without taking up the resources in the production environment. Daily processing will go faster since the database that is being accessed is smaller, and there are fewer records that the system will need to look through when determining what information is to be used for a given process.  Users have reported reductions of 40-60% in database size.  Data intensive processes such as MRP regeneration, invoice posting, subsidiary ledger posting to General Ledger, etc. post a dramatic improvement in processing time.  The associated database size reduction also reduces daily backup time, as well.

Save an incredible amount of time

Can this be accomplished using the purge processes already in Macola?  Absolutely.  However, the time required to purge each and every file using the native system tools will take hours and hours, spread over several days or weeks, since you can't purge the system while people are using it.  The data retention utility will accomplish the entire job within an hour or so.  That's a huge gain in productivity for a system administrator, and encourages and facilitates regular maintenance to keep your Macola system operating at peak performance.