Searching question

[royalcheese]

Hi all I know its friday and I have a question on searching

I have inherited a large DB , and want to find any tables that have the value '161941' in any of the cells , is this possible to do (relitively) simply

Many thanks

C

 

[gmmastros]

This may have errors depending on the data types you have in your tables.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Integer[/color] [COLOR=blue]Identity[/color](1,1), Query [COLOR=blue]VarChar[/color](8000))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Query)
[COLOR=blue]Select[/color] 	[COLOR=red]'If Exists(Select * From ['[/color] + table_schema + [COLOR=red]'].['[/color] + Table_Name + [COLOR=red]'] Where Convert(VarChar(20), ['[/color] + Column_Name + [COLOR=red]']) = ''161941'')
[/color]        [COLOR=blue]Select[/color] * [COLOR=blue]From[/color] [[COLOR=red]' + Table_Name + '[/color]] [COLOR=blue]Where[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [[COLOR=red]' + Column_Name + '[/color]]) = [COLOR=red]''[/color]161941[COLOR=red]'''
[/color][COLOR=blue]from[/color] 	information_Schema.columns
[COLOR=blue]Where[/color]	Data_Type not in ([COLOR=red]'uniqueidentifier'[/color],[COLOR=red]'image'[/color])

[COLOR=blue]Declare[/color] @i [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @Max [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @Query [COLOR=blue]VarChar[/color](8000)

[COLOR=blue]Select[/color] @i = 1, @Max = [COLOR=#FF00FF]Max[/color]([COLOR=#FF00FF]RowId[/color])
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]While[/color] @i <= @Max
  [COLOR=blue]Begin[/color]

    [COLOR=blue]Select[/color] @Query = Query
    [COLOR=blue]From[/color]   @Temp
    [COLOR=blue]Where[/color]  [COLOR=#FF00FF]RowId[/color] = @i

    [COLOR=blue]Exec[/color] (@Query)

    [COLOR=blue]Set[/color] @i = @i + 1

  [COLOR=blue]End[/color]

do not expect this to be quick.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[royalcheese]

ill give it a go , many thanks