May 18, 2011 at 9:37 am
I want to search the SQL 2005 entire database for a possible email Address string. what is the best way to do it. thanks for your help.
May 18, 2011 at 9:51 am
"Best" will depend on your database and what else is going on in it.
You could use sys.columns to identify all table columns that can contain textual data (varchar, char, nvarchar, nchar, text, ntext), and then query all of those dynamically. The obsolete text and ntext data types will require conversion to varchar(max) or nvarchar(max) before you can use regular string expressions on them.
Are you looking for just data where the e-mail address is the whole value, or where it's part of the value? Like, "My e-mail address is email@domain.com, and my name is ...", would be only part of the string. Do you want to find ones like that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 18, 2011 at 10:06 am
thanks GSquared for the quick reply.
I need to search entire db for a Pattern like email address. Any full text or part of text that looks like email address.
May 19, 2011 at 6:55 am
Then your best bet will be to dynamically create a query that checks all string datatypes in all tables.
Something like:
select 'select ' + case system_type_id when 39 then 'convert(varchar(max), '
when 99 then 'convert(nvarchar(max), '
else '' end
+'[' + columns.name + '] ' +
case system_type_id when 39 then ')'
when 99 then ')'
else ''
end +
' from [' + tables.name + '] where [' + case system_type_id when 39 then 'convert(varchar(max), '
when 99 then 'convert(nvarchar(max), '
else '' end
+'[' + columns.name + '] ' +
case system_type_id when 39 then ')'
when 99 then ')'
else ''
end + '] like ''%MyEmail@MyDomain.com%'';'
from sys.columns
inner join sys.tables
on columns.object_id = tables.object_id
where tables.type = 'U'
and columns.system_type_id in (
select system_type_id
from sys.types
where collation_name is not null);
(Sorry for the messy layout. I'm not at my usual computer and don't have RedGate SQL Prompt to do my layout for me, and I'm a complete spaz without that these days.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2011 at 7:03 am
thanks for the update, i found your query useful.,
May 19, 2011 at 11:04 am
I've got my prompt here...
select
'select ' + case system_type_id
when 39 then 'convert(varchar(max), '
when 99 then 'convert(nvarchar(max), '
else ''
end + '[' + columns.name + '] ' + case system_type_id
when 39 then ')'
when 99 then ')'
else ''
end + ' from ['
+ tables.name + '] where [' + case system_type_id
when 39 then 'convert(varchar(max), '
when 99 then 'convert(nvarchar(max), '
else ''
end + '[' + columns.name + '] '
+ case system_type_id
when 39 then ')'
when 99 then ')'
else ''
end + '] like ''%MyEmail@MyDomain.com%'';'
from
sys.columns
inner join sys.tables
on columns.object_id = tables.object_id
where
tables.type = 'U'
and columns.system_type_id in ( select
system_type_id
from
sys.types
where
collation_name is not null ) ;
May 20, 2011 at 6:48 am
searching for strings is often best accomplished by using Full Text Search. I note that the 2008+ versions are much better than the 2005 one.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply