Search All Columns in all tables

  • R Michael

    SSCarpal Tunnel

    Points: 4211

    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

    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')

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply