InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:53:17 GMT20http://www.sqlservercentral.com/Forums/Topic401806-329-1.aspx[b]The actual post is Ok for the tables which are of One word for others below is the modified query.[/b]DECLARE @searchSQL AS VARCHAR(MAX)DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%Davolio%'SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' + CAST(Ordinal_position AS VARCHAR(MAX)) + ' THEN '' OR '' + IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' + CAST(Ordinal_position AS VARCHAR(MAX)) + ' THEN IC.Column_Name + '' LIKE ''''' + @SearchItem + ''''''' END), '''') AS NVARCHAR(max))' + CHAR(13))FROM INFORMATION_SCHEMA.Columns GROUP BY Ordinal_PositionORDER BY Ordinal_PositionEXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM (SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as tbl, * FROM ['' + CAST(TABLE_SCHEMA AS VARCHAR(MAX)) + '']. [b][[/b]'' + CAST(TABLE_NAME AS VARCHAR(MAX)) + ''[b]] [/b] WHERE '' + ' + @searchSQL + ' AS queryFROM INFORMATION_SCHEMA.Columns ICWHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')GROUP BY TABLE_NAME, TABLE_SCHEMA) S')Wed, 06 Jan 2010 05:47:35 GMTShrikrishnaGhttp://www.sqlservercentral.com/Forums/Topic401806-329-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/31859/">Search All Columns in all tables</A>[/B]Sun, 23 Sep 2007 21:09:56 GMTRobert Michael Cary