how to search through all varchar/nvarchar or text fields for a string

So I’ve been playing around with HyperV extensively.

I took the liberty of renaming my System Center VMM server today.. Didn’t think too much about it first.. I just got tired of typing WinVMM2012R2.. so I shortened it to VMM.

Well.. after a couple of reboots.. and renaming the Server in SQL Server.. I still wasn’t able to get in.

So then I started looking for .CONFIG files that might have a servername or something that needed to be changed.  Well.. that didn’t work either.. .

So now I’m stuck looking through a database for a simple string replacement.

I’ve used this method for years and years and years in order to find the data that I’m *REALLY* looking for inside of SQL Server tables.  I get tired of writing the same scripts week in and week out.. so I try to share them on my blog..

Hope it helps someone, somewhere.. on this great big internet.
Obviously, you’ll need to replace the string WINVMM2012R2 with the string that you’re REALLY looking for 🙂

ps – you MIGHT need to change your settings under tools, options, query results, sql server, results to text.
The default there is only 255 characters, I usually expand that out to ~2000 characters.

-Aaron

 



select
'select '
+ char(39)
+ so.name
+ char(39)
+ ' as tblName, '
+ char(39)
+ sc.name
+ char(39)
+ ' as colName, ['
+ sc.name
+ '] as TheCol, * FROM ['
+ so.name
+ '] where ['
+ sc.name
+ '] like '
+ CHAR(39)
+ '%winvmm2012r2%'
+ CHAR(39)
+ CHAR(10)
+ 'go'
from sysobjects so
inner join syscolumns sc
on so.id = sc.id
where so.xtype = 'u'
and TYPE_NAME(sc.xtype) in ('varchar', 'nvarchar', 'text', 'ntext')

This entry was posted in SQL Server and tagged . Bookmark the permalink.