title: Search all tables in database and return a value?
body: <p>I'm using SQL Server 2008 and working off of the follow example <a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2" rel="nofollow">HERE</a>.</p>
  
  <p>Below is the code I have so far. (The only difference is a turned it into a query from a stored procedure and removed schema name which wasn't necessary.)</p>
  
  <p>What I'd like to do is add another column to the result and give the first column's data value. In other words if each table starts off with a Primary Key, my result will include the Primary Key of the row where the data was found. Is this even possible? Thanks.</p>
  
  <pre><code>DECLARE @DataToFind nvarchar(MAX) = ''
  DECLARE @ExactMatch BIT = 0
  
  DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
  
      INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
      SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
      FROM    Information_Schema.Columns AS C
              INNER Join Information_Schema.Tables AS T
                  ON C.Table_Name = T.Table_Name
          AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
      WHERE   Table_Type = 'Base Table'
              And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
  
  
  DECLARE @i INT
  DECLARE @MAX INT
  DECLARE @TableName sysname
  DECLARE @ColumnName sysname
  DECLARE @SchemaName sysname
  DECLARE @SQL NVARCHAR(4000)
  DECLARE @PARAMETERS NVARCHAR(4000)
  DECLARE @DataExists BIT
  DECLARE @SQLTemplate NVARCHAR(4000)
  
  SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                              THEN 'If Exists(Select *
                                            From   ReplaceTableName
                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                         = ''' + @DataToFind + '''
                                            )
                                       Set @DataExists = 1
                                   Else
                                       Set @DataExists = 0'
                              ELSE 'If Exists(Select *
                                            From   ReplaceTableName
                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                         Like ''%' + @DataToFind + '%''
                                            )
                                       Set @DataExists = 1
                                   Else
                                       Set @DataExists = 0'
                              END,
          @PARAMETERS = '@DataExists Bit OUTPUT',
          @i = 1
  
  SELECT @i = 1, @MAX = MAX(RowId)
  FROM   @Temp
  
  WHILE @i &lt;= @MAX
      BEGIN
          SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
          FROM    @Temp
          WHERE   RowId = @i
  
  
          PRINT @SQL
          EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
  
          IF @DataExists =1
              UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
  
          SET @i = @i + 1
      END
  
  SELECT  TableName, ColumnName
  FROM    @Temp
  WHERE   DataFound = 1
  </code></pre>