sql server - Search SQL DB's For A Specific Word -

sql server - Search SQL DB's For A Specific Word -

i new sql , have no experience ever in please bear me question.

i need know if possible search sql database specific word , if how?

we going through rebranding project , need in our cms (content management system) database reference email address. need search is:

.co.uk

below screenshot of database in question containing tables, cant me head around sql , have had no joy on google trying find answer.

i need search in database don't know tables, views, column names etc content sits in it's spread across them all.

there other tables need search reply provided can modify search these.

db's aren't meant such vague search descriptions, should have definition or model or requirement specs describe values exist.

but of course, opt insanely slow method of doing using dynamic sql.

i made right fast , tested fast, should work:

set nocount on if object_id('tempdb..#searchtable') not null drop table #searchtable if object_id('tempdb..#results') not null drop table #results create table #searchtable (rownum int identity(1,1), searchclause varchar(2000) collate database_default) insert #searchtable (searchclause) select 'select top 1 '''+tab.name+''', '''+c.name+''' ['+s.name+'].['+tab.name+'] ' +case when t.name <> 'xml' '['+c.name+'] ''%.co.uk%'' , ['+c.name+'] ''%@%''' else 'cast(['+c.name+'] varchar(max)) ''%.co.uk%'' , cast(['+c.name+'] varchar(max)) ''%@%''' end searchclause sys.tables tab bring together sys.schemas s on s.schema_id = tab.schema_id bring together sys.columns c on c.object_id = tab.object_id bring together sys.types t on t.user_type_id = c.user_type_id tab.type_desc = 'user_table' , (t.name '%char%' or t.name '%xml%') , case when c.max_length = -1 10 else c.max_length end >= 6 -- search through sufficiently long column create table #results (rownum int identity(1,1), tablename varchar(256) collate database_default, colname varchar(256) collate database_default) declare @rownum_now int, @rownum_max int, @sqlcmd varchar(2000), @statusstring varchar(256) select @rownum_now = min(rownum), @rownum_max = max(rownum) #searchtable while @rownum_now <= @rownum_max begin select @sqlcmd = searchclause #searchtable rownum = @rownum_now insert #results exec(@sqlcmd) set @statusstring = cast(@rownum_now varchar(25))+'/'+cast(@rownum_max varchar(25))+', time: '+convert(varchar, getdate(), 120) raiserror(@statusstring, 10, 1) nowait select @rownum_now = @rownum_now + 1 end set nocount on select 'this table , column contains strings ".co.uk" , "@"' information, tablename, colname #results -- uncomment drop created temp tables --if object_id('tempdb..#searchtable') not null -- drop table #tablecols --if object_id('tempdb..#results') not null -- drop table #results

what does, search db user-created tables schemas, have (n)char/(n)varchar/xml columns of sufficient length, , search each of them 1 1 until @ to the lowest degree 1 match found, moves next 1 on list. match defined string or xml cast string, contains text ".co.uk" , "@"-sign somewhere in there.

it show progress of script (how many searchable table.column combinations have been found , 1 on list running, current timestamps downwards seconds) on messages tab. when ready, show tables , column names contained @ to the lowest degree 1 match.

so list, you'll have search through tables , columns manually find how many , kinds of matches there are, , want do.

edit: 1 time again disregarded using sysnames sysobjects, i'll modify later if needed.

sql sql-server tsql