How do I find a value anywhere in a GoldMine SQL Server Database?

Version 1

    Details

    How do I find a value anywhere in a GoldMine SQL Server Database?

    Many times it is necessary to find a certain RECID in the database where it is unclear in which or how many GoldMine database tables the specific value will appear - for example LINKRECID in MAILBOX table, or FRECID in TLog tables. This article provides 2 possibilities how to find such values.


    Resolution

    [DISCLAIMER]
    - The following information is provided as-is without warranty of any kind and are completely outside the support boundaries of GoldMine Technical Support.
    - These queries are sometimes used by the GoldMine Technical Support Team in order to find certain information within a GoldMine database and from experience seem to work in most cases or for the desired approach.
    - GoldMine Technical Support will not assist in modifying or troubleshooting any of the below queries. We highly encourage to consult also an (Microsoft) SQL DBA/specialist to discuss these scripts.
    - Further information which is  provided by GoldMine Technical Support only as-is and without any  warranty of any kind can be found on the below threads. Please keep in  mind that any non GoldMine or non Heat Software links are only provided  as-is and content and availability may change at any time without any  notice
    http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database?ref=driverlayer.com/web
    http://www.sqlservercentral.com/Forums/Topic1232230-391-1.aspx


    [RESOLUTION 1 - via a script - usually only for a one time use]

    - The following approach is via a query on the specific GoldMine database, which requires the complete script to be copied and pasted (executed) every time a value needs to be queried
    - Does not search: IMAGE fields
    - as this script creates a long string for searching each column per table, this may result that certain tables are not searched and error messages may appear in the Messages tab after execution,. In such a case the specific table needs to be searched separately for example
    IF EXISTS(SELECT NULL FROM [dbo].[CONTACT1] WHERE 1 = 0 OR [ACCOUNTNO] LIKE '%H3CK39L#KWW6 W<%' OR [COMPANY] LIKE '%H3CK39L#KWW6 W<%' OR [CONTACT] LIKE '%H3CK39L#KWW6 W<%' OR ......OR [U_KEY3] LIKE '%H3CK39L#KWW6 W<%' OR [U_KEY4] LIKE '%H3CK39L#KWW6 W<%' OR [U_KEY5] LIKE '%H3
    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark after the character string '%H3'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '%H3'.
     

    [STEPS]

    1. SQL Server Management Studio
    2. Button New Query
    3. Make sure to select the desired database from the database drop down (otherwise add to the script the USE DB command)
    4. Copy and paste the following query and adjust @SEARCHTERM variable to the desired value

    -------------------------------------------------
    -- SEARCH ALL COLUMNS IN ALL TABLES IN A DATABASE FOR A STRING (%XYZ%)
    -- DOES NOT SEARCH: IMAGE FIELDS AND EXACT SEARCH ALWAYS FOR MONEY
    --------------------------------------------------------------------------------
    DECLARE @SEARCHTERM NVARCHAR(4000) -- CAN BE MAX FOR SQL2005+
    DECLARE @COLUMNNAME SYSNAME
    --------------------------------------------------------------------------------
    SET @SEARCHTERM = N'%H3CK39L#KWW6 W<%' -- TERM TO BE SEARCHED FOR
    SET @COLUMNNAME = N'' -- USE TO RESTRICT THE SEARCH TO CERTAIN COLUMNS, WILDCARDS OKAY, NULL OR EMPTY STRING FOR ALL COLS

    SET NOCOUNT ON

    DECLARE @TABCOLS TABLE (
          ID INT NOT NULL PRIMARY KEY IDENTITY
        , TABLE_SCHEMA SYSNAME NOT NULL
        , TABLE_NAME SYSNAME NOT NULL
        , COLUMN_NAME SYSNAME NOT NULL
        , DATA_TYPE SYSNAME NOT NULL)
    INSERT INTO @TABCOLS (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE)
        SELECT T.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE
        FROM INFORMATION_SCHEMA.TABLES T
            JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
                AND T.TABLE_NAME = C.TABLE_NAME
        WHERE 1 = 1
            AND T.TABLE_TYPE = 'BASE TABLE'
            AND C.DATA_TYPE NOT IN ('IMAGE', 'SQL_VARIANT')
            AND C.COLUMN_NAME LIKE CASE WHEN LEN(@COLUMNNAME) > 0 THEN @COLUMNNAME ELSE '%' END
        ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION

    DECLARE
          @TABLE_SCHEMA SYSNAME
        , @TABLE_NAME SYSNAME
        , @COLUMN_NAME SYSNAME
        , @DATA_TYPE SYSNAME
        , @EXISTS NVARCHAR(4000) -- CAN BE MAX FOR SQL2005+
        , @SQL NVARCHAR(4000) -- CAN BE MAX FOR SQL2005+
        , @WHERE NVARCHAR(4000) -- CAN BE MAX FOR SQL2005+
        , @RUN NVARCHAR(4000) -- CAN BE MAX FOR SQL2005+


    WHILE EXISTS (SELECT NULL FROM @TABCOLS) BEGIN
        SELECT TOP 1
              @TABLE_SCHEMA = TABLE_SCHEMA
            , @TABLE_NAME = TABLE_NAME
            , @EXISTS = 'SELECT NULL FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE 1 = 0'
            , @SQL = 'SELECT ''' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + ''' AS TABLE_NAME, * FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE 1 = 0'
            , @WHERE = ''
        FROM @TABCOLS
        ORDER BY ID

        WHILE EXISTS (SELECT NULL FROM @TABCOLS WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME) BEGIN
            SELECT TOP 1
                  @COLUMN_NAME = COLUMN_NAME
                , @DATA_TYPE = DATA_TYPE
            FROM @TABCOLS
            WHERE TABLE_SCHEMA = @TABLE_SCHEMA
                AND TABLE_NAME = @TABLE_NAME
            ORDER BY ID

            -- SPECIAL CASE FOR MONEY
            IF @DATA_TYPE IN ('MONEY', 'SMALLMONEY') BEGIN
                IF ISNUMERIC(@SEARCHTERM) = 1 BEGIN
                    SET @WHERE = @WHERE + ' OR [' + @COLUMN_NAME + '] = CAST(''' + @SEARCHTERM + ''' AS ' + @DATA_TYPE + ')' -- COULD ALSO CAST THE COLUMN AS VARCHAR FOR WILDCARDS
                END
            END
            -- SPECIAL CASE FOR XML
            ELSE IF @DATA_TYPE = 'XML' BEGIN
                SET @WHERE = @WHERE + ' OR CAST([' + @COLUMN_NAME + '] AS NVARCHAR(MAX)) LIKE ''' + @SEARCHTERM + ''''
            END
            -- SPECIAL CASE FOR DATE
            ELSE IF @DATA_TYPE IN ('DATE', 'DATETIME', 'DATETIME2', 'DATETIMEOFFSET', 'SMALLDATETIME', 'TIME') BEGIN
                SET @WHERE = @WHERE + ' OR CONVERT(NVARCHAR(50), [' + @COLUMN_NAME + '], 121) LIKE ''' + @SEARCHTERM + ''''
            END
            -- SEARCH ALL OTHER TYPES
            ELSE BEGIN
                SET @WHERE = @WHERE + ' OR [' + @COLUMN_NAME + '] LIKE ''' + @SEARCHTERM + ''''
            END

            DELETE FROM @TABCOLS WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME

        END

        SET @RUN = 'IF EXISTS(' + @EXISTS + @WHERE + ') BEGIN ' + @SQL + @WHERE + ' PRINT ''' + @TABLE_NAME + ''' END'
        PRINT @RUN
        EXEC SP_EXECUTESQL @RUN

    END

    SET NOCOUNT OFF


    5. Execute
    >> RESULT:




    [RESOLUTION 2 - via a stored procedure - also for future use]

    - The following approach is via a creating a stored procedure on the specific GoldMine database
    - Does not search: IMAGE fields

    [STEPS]

    1. SQL Server Management Studio
    2. Button New Query
    3. Make sure to select the desired database from the database drop down (otherwise add to the script the USE DB command)
    4. Copy and paste the script for creating the stored procedure

    CREATE PROC SearchAllTables
    (
        @SearchStr nvarchar(100)
    )
    AS
    BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

        SET NOCOUNT ON

        DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
        SET  @TableName = ''
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

        WHILE @TableName IS NOT NULL
        BEGIN
            SET @ColumnName = ''
            SET @TableName =
            (
                SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                FROM     INFORMATION_SCHEMA.TABLES
                WHERE         TABLE_TYPE = 'BASE TABLE'
                    AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                    AND    OBJECTPROPERTY(
                            OBJECT_ID(
                                QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                 ), 'IsMSShipped'
                                   ) = 0
            )

            WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            BEGIN
                SET @ColumnName =
                (
                    SELECT MIN(QUOTENAME(COLUMN_NAME))
                    FROM     INFORMATION_SCHEMA.COLUMNS
                    WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                        AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                        AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                        AND    QUOTENAME(COLUMN_NAME) > @ColumnName
                )
       
                IF @ColumnName IS NOT NULL
                BEGIN
                    INSERT INTO #Results
                    EXEC
                    (
                        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                        FROM ' + @TableName + ' (NOLOCK) ' +
                        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                    )
                END
            END   
        END

        SELECT ColumnName, ColumnValue FROM #Results
    END

    5. Execute



    6. Once the stored procedure was created successfully >> the below query can be used to find any value within the specific database

    EXEC SearchAllTables 'Search Term'

    7. When the stored procedure should be deleted again, use the following query

    DROP PROC SearchAllTables
     
    >> RESULT: