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.