Find All Databases with…?

I work with a lot of databases. Currently, we have over 500 databases on our one SQL box. Whenever I have to go in and modify a a specific XML section, I need a quick way to find any DataBase that has that specific XML text. I came up with a quick Script that goes through every database, searches for the table I want to see if it contains my XML text.

Here’s a script that has done well for me:

Exec sp_MSforeachdb @command1= 'USE ? IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' 
begin 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''[dbo].[tblProcessConfigurations]'') AND type in (''U'')) 
BEGIN 
SELECT * FROM dbo.tblProcessConfigurations 
END 
end'

What this will do is print out the contents of every tblProcessConfigurations of every database on my server. However, I had an issue – what Databases were they from?

I add the simple function: db_name() to my query and voila:

Exec sp_MSforeachdb @command1= 'USE ? IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' 

begin 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''[dbo].[tblProcessConfigurations]'') AND type in (''U'')) 

BEGIN 

print db_name() 

SELECT * FROM dbo.tblProcessConfigurations 

END 

end'

I can now document every DataBase that will need to be updated, and what the Updates need to be.