Search for Text in all DB objects

The other day I needed to alter a bunch of sprocs, views, and UDFs, as they had hard-coded references to other databases. I needed to change these to target a different database for testing some new indexes.

This bit of SQL saved me a bunch of time:

SELECT OBJECT_NAME(id) AS ObjectName, [text] as ObjectDefinition
FROM sys.syscomments
WHERE [text] like '%\[Foo\]%' ESCAPE '\'
FOR XML PATH('Row'), root('Table')

It outputs a single xml doc that contains the definition of every database object that contained your search terms. This allowed me to easily search through them and update accordingly.

Popular posts from this blog

Taking a memory dump of a w3wp process

sp_blitzIndex

GitLab Badges