A new MSDN thread question prompted me to write a new variation of my original blog post 'How to search a string value in all columns in all tables in a database'. This blog will show how to search for an integer value in all integer (smallint, tinyint, int, bigint) columns in all tables in the database. This time I will not create a separate procedure to search for an integer value in a table, but just show a complete script involving a loop through all tables in a database. This loop may execute a long time in a huge database, so test it only on a small database. There are many ways to enhance it, I just show the idea and appreciate your comments/critique.

-- First declare variables for test
USE AdventureWorks 
DECLARE @SearchValue INT 
-- Set values for variables for test
SET NOCOUNT ON;
SELECT  @SearchValue = 30

DECLARE @Columns NVARCHAR(MAX) ,
    @Cols NVARCHAR(MAX) ,
    @Table_Name SYSNAME ,
    @PkColumn NVARCHAR(MAX) ,
    @Table_Schema SYSNAME ,
    @SQL NVARCHAR(MAX) 

IF OBJECT_ID('TempDB..#Result', 'U') IS NOT NULL 
    DROP TABLE #Result 
CREATE TABLE #RESULT
    (
      [PK COLUMN] NVARCHAR(MAX) ,
      [COLUMN VALUE] BIGINT ,
      [COLUMN Name] SYSNAME ,
      [TABLE SCHEMA] SYSNAME ,
      [TABLE Name] SYSNAME
    )
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
    SELECT  Table_Schema ,
            Table_Name
    FROM    INFORMATION_SCHEMA.Tables
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY Table_Schema ,
            Table_Name
    
OPEN curAllTables
WHILE 1 = 1 
    BEGIN
        FETCH curAllTables
    INTO @Table_Schema, @Table_Name    
        IF @@FETCH_STATUS <> 0  -- Loop through all tables in the database
            BREAK
    

        PRINT CHAR(13) + 'Processing ' + QUOTENAME(@Table_Schema) + '.'
            + QUOTENAME(@Table_Name)
-- Get all int columns
        SET @Columns = STUFF(( SELECT   ', ' + QUOTENAME(Column_Name)
                               FROM     INFORMATION_SCHEMA.COLUMNS
                               WHERE    DATA_TYPE LIKE '%int'
                                        AND TABLE_NAME = @Table_Name
                                        AND table_schema = @Table_Schema
                               ORDER BY COLUMN_NAME
                             FOR
                               XML PATH('')
                             ), 1, 2, '')
        IF @Columns IS NULL 
            BEGIN
                PRINT 'No int columns in the ' + QUOTENAME(@Table_Schema)
                    + '.' + QUOTENAME(@Table_Name)
                CONTINUE
            END  

-- Get columns for select statement - we need to convert all columns to bigint
        SET @Cols = STUFF(( SELECT  ', cast(' + QUOTENAME(Column_Name)
                                    + ' as bigint) as '
                                    + QUOTENAME(Column_Name)
                            FROM    INFORMATION_SCHEMA.COLUMNS
                            WHERE   DATA_TYPE LIKE '%int'
                                    AND TABLE_NAME = @Table_Name
                            ORDER BY COLUMN_NAME
                          FOR
                            XML PATH('')
                          ), 1, 2, '')
 
-- Create PK column(s)
        SET @PkColumn = STUFF(( SELECT  N' + ''|'' + ' + ' cast('
                                        + QUOTENAME(CU.COLUMN_NAME)
                                        + ' as nvarchar(max))'
                                FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                                        INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME
                                                              AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
                                                              AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
                                WHERE   TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                                        AND TC.TABLE_SCHEMA = @Table_Schema
                                        AND TC.TABLE_NAME = @Table_Name
                                ORDER BY CU.COLUMN_NAME
                              FOR
                                XML PATH('')
                              ), 1, 9, '') 

        IF @PkColumn IS NULL 
            SELECT  @PkColumn = 'cast(NULL as nvarchar(max))' 
    
 -- set select statement using dynamic UNPIVOT
 
        SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema, '''')
            + 'as [Table Schema], ' + QUOTENAME(@Table_Name, '''')
            + ' as [Table Name]' + ' from 
  (select ' + @PkColumn + ' as [PK Column], ' + @Cols + ' from '
            + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name)
            + ' )src UNPIVOT ([Column Value] for [Column Name] IN ('
            + @Columns + ')) unpvt 
 WHERE [Column Value] = @SearchValue'
 
 --print @SQL -- if we get errors, we may want to print generated SQL
        INSERT  #RESULT
                ( [PK COLUMN] ,
                  [COLUMN VALUE] ,
                  [COLUMN Name] ,
                  [TABLE SCHEMA] ,
                  [TABLE Name]
                
                )
                EXECUTE sp_ExecuteSQL @SQL, N'@SearchValue int', @SearchValue
                PRINT 'Found ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' records in ' 
                       + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name)                
    END
CLOSE curAllTables
DEALLOCATE curAllTables
SELECT  *
FROM    #RESULT
ORDER BY [TABLE SCHEMA] ,
        [TABLE Name]

You can see that we can apply the same idea if we want to search for date field or numerical field.