2

Is there a way to find all instances of a string in all objects of all databases on one server?

The reason behind this question is that we need to change a reference to another database (located on another server).

So, we need to find every view, stored procedure, function, etc. that has a reference to the old server. This search also needs to include objects that are not specific to a single database, like system jobs.

So far, the best way I know of doing this is to use the Generate Script wizard on each database individualy and then search for the string in the generated files. The problem with this approach is that it ignores system jobs.

Is there a better way of doing this?

UPDATE 1

I looked into the sys.sql_modules table and that looks like a good alternative to the Generate Scripts wizard.

I have a query for one database that works fine by itself

select 
'MyDatabaseName' as DbName,
o.name as ObjectName,
o.type_desc as ObjectType,
m.definition as Definition
from MyDatabaseName.sys.sql_modules m 
inner join sys.objects o on m.object_id = o.object_id
WHERE m.definition Like '%MyString%'

But when I try to use it with sp_msforeachdb, I don't get the same amount of results.

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL

Even when removing the condition for MyString I still don't get the same number of results (from 267 to 27).

Using msforeachdb would really help me because I need to run this on many databases over many servers.

UPDATE 2

Found the issue in my query, it was missing a [?] in front of the joined sys.objects. Here's the corrected query

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join [?].sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL
  • 1
    Have you considered using sys.sql_modules and checking the definition column for that value? It will give you all the objects. It's also worth considering keeping a dummy copy of the existing database and using synonyms to refer to the linked server versions, thus not having to change your code (performance problems of linked servers aside all of this). – Nic Apr 3 '17 at 19:39
  • Did the database you have currently referenced in your code change or something else, such as the database server? – John Eisbrener Apr 3 '17 at 20:41
  • @Nic - don't think SQL code embedded in jobs is in sys.sql_modules. A similar approach could probably be taken (with, I think msdb.dbo.sysjobsteps, but I could be wrong). – RDFozz Apr 3 '17 at 20:51
  • Note that, with any find and replace scheme, one must allow for variations in the references. Each part of each 4-part name might or might not be in square brackets, and when the schema is [dbo] is can be left out (not 100% certain that's true with 4-part names, but better safe than sorry). So, a lot of ways someone might have said the same thing - no matter whether you have internal standards as to how that should be written or not. – RDFozz Apr 3 '17 at 20:55
  • That said - @Nic 's approach might be better than scripting out the entire DB, as you can at least narrow the output down to what you need to touch, as opposed to a text file with every DB object in it (or a plethora of text files to go through, your pick). With the caveats about searching 4-part names.... – RDFozz Apr 3 '17 at 20:58
4

sp_msforeachdb is undocumented. There are viable alternatives to it.

Try changing:

SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

to:

SET @SQL='USE [?]; select Db_Name(), o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

As originally written, the @SQL's sys.objects will reference the database being used for sp_msforeachdb's execution and thus the statement's 2 object_id values may or may not join.

  • Thanks for the answer. I just found a solution before seeing your answer. I'll consider the changes you made to it, but so far what I have works. – Shadowxvii Apr 4 '17 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.