Here's what I came up with using dynamic SQL. To retrieve the column names dynamically it uses a function which I've included below.

DECLARE

@strSearch NVARCHAR(4000)

,@strSQL NVARCHAR(MAX)

,@strPath NVARCHAR(250)

,@pDate DATETIME

,@pDateSearchCol NVARCHAR(20)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[ColVal] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

SET @strSearch = N'%contentpane%' --ex 1

SET @strPath = N'LocalTestDB.dbo.Tabs' --ex1

--SET @strSearch = N'22%' --ex 2

--SET @strPath = N'LocalTestDB.dbo.TabModules' --ex2

SET @pDate = '2013-01-01'

SET @pDateSearchCol = 'LastModifiedOnDate'

SET @strSQL = N''

;WITH cteCols

AS

(

SELECT ORDINAL_POSITION, COLUMN_NAME

FROM dbo.itvfGetColumnNames(''+@strPath+'')

WHERE ORDINAL_POSITION > 0

)

SELECT

@strSQL = @strSQL +

N'SELECT '+ c.COLUMN_NAME + ' ' +

N'FROM '+@strPath+'' + ' ' +

N'WHERE DATEDIFF(day,'+@strPath+'.'+@pDateSearchCol+','''+CONVERT(VARCHAR(30),@pDate,121)+''') > 0 '+

N'AND ' + c.COLUMN_NAME + ' LIKE '''+@strSearch+'''; '+CHAR(10)+CHAR(13)

FROM

cteCols c

INSERT INTO #TempTable

EXEC sp_executeSQL @strSQL

SELECT COUNT(*) AS NumRows FROM #TempTable

CREATE FUNCTION [dbo].[itvfGetColumnNames]

(

@SourceFullPath SYSNAME

)

RETURNS TABLE

AS

RETURN

(

WITH Keys

AS (

SELECT

iso.COLUMN_NAME

,iso.COLUMN_DEFAULT

,iso.ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.COLUMNS AS iso

WHERE

iso.TABLE_CATALOG = PARSENAME(@SourceFullPath,3)

AND iso.TABLE_SCHEMA = PARSENAME(@SourceFullPath,2)

AND iso.TABLE_NAME = PARSENAME(@SourceFullPath,1)

)

SELECT TOP 100 PERCENT

ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS rownum

,COLUMN_NAME

,COLUMN_DEFAULT

,ORDINAL_POSITION

FROM

Keys

ORDER BY

ORDINAL_POSITION

/*

SELECT * FROM dbo.itvfGetColumnNames('[FullyQualifiedThreePartDBName]')

*/

)