"Roy Harvey" wrote:

> On Thu, 16 Feb 2006 16:33:27 -0800, "culam"
> <cu***@discussions.microsoft.com> wrote:
>
> >Is there a way to search entire database for a value "HTML" that stored on
> >any given columns?
> >
> >Thanks,
> >Culam
>
> Brute force.  Query the table INFORMATION_SCHEMA.Columns where the
> DATA_TYPE column is either CHAR, VARCHAR, NCHAR or NVARCHAR.  Use that
> information to build SELECT commands that performa the test you want
> against those columns, then run them.
>
> SELECT 'SELECT ' + COLUMN_NAME + ', * ' +
>        '  FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
>        ' WHERE ' + COLUMN_NAME + ' LIKE ''%HTML%'''
>   FROM INFORMATION_SCHEMA.Columns
>  WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
>    AND TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
>
> You might want to change it so the table name appears somewhere too.
>
> You could also get fancy and put this in a cursor, with an EXEC to run
> dynamic SQL.
>
> Roy
>