The Attivo Blog

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