How to Find a Value in the HEAT Help Desk Database.

Version 1

    Details

    Is there a SQL query to find a specific value throughout the entire HEAT database?


    Resolution

    Copy and paste the following into SQL Server Management Studio:

    --Run the uncommented EXEC/GO after the Stored Procedure has been created below

    --EXEC SearchAllTables 'Employee'

    --GO

     

     

    --Run this first in order to create the proper stored procedure. 

    --After completed, uncomment the --EXEC SearchAllTables and GO query above.

    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