Find an instance of a character in a database

Question

Is there a relatively easy way to find an instance of a character, say a "|" or a "~" or a "&", in a database?

It could be in any field...in any table.

There are around 400 to 500 tables in the database in total.

Answer 1

the easiest way is to bulk export all the data for all of the tables and then search/grep for the string in question.

http://msdn.microsoft.com/en-us/library/ms187042.aspx

Answer 2

You could export the database to one big SQL script using the SQL Management Console. Once you have the text file you can use notepad/grep/etc. to find the characters you're looking for.

Answer 3

This code will identify each table and varchar column in your database. It could be used to generate a select statement for each table and column to search for rows where that column contains the characters you're looking for.

select o.id, o.name, c.name, t.name, t.*
from sysobjects o
join syscolumns c on c.id = o.id
join systypes t on t.xtype = c.xtype
where o.xtype = 'U'
      and t.status = 0
      and t.name like '%varchar'
order by o.name, c.name

Depending on the size and indexing of your tables this may or may not be a good idea.