Search All Columns in all tables

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  September 23, 2007 at 9:09 pm

  #177932

  Comments posted to this topic are about the item Search All Columns in all tables

  SQL guy and Houston Magician

• ShrikrishnaG

  SSC Journeyman

  Points: 81

  More actions

  January 6, 2010 at 5:47 am

  #1099487

  The actual post is Ok for the tables which are of One word for others below is the modified query.

  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_Position

  ORDER BY Ordinal_Position

  EXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM

  (SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as tbl, * FROM ['' +

  CAST(TABLE_SCHEMA AS VARCHAR(MAX)) + '']. ['' + CAST(TABLE_NAME AS VARCHAR(MAX)) + ''] WHERE '' + ' + @searchSQL + ' AS query

  FROM INFORMATION_SCHEMA.Columns IC

  WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')

  GROUP BY TABLE_NAME, TABLE_SCHEMA) S')