SQL Server 2005 • Search the Entire Server

Listing 2. dba_searchDBServer searches all the databases on the current server for the supplied string (given as the parameter @StringToFind). It is essentially a wrapper for the previous utility (dba_searchDB), which is called repeatedly using the database names supplied by the undocumented stored procedure sp_MSForEachDB. You validate the supplied string to ensure it has the correct format for the search. You should create this utility in the master database.

CREATE PROC dba_SearchDBServer
        @StringToFind NVARCHAR(1000)
AS

DECLARE @SQL NVARCHAR(4000)

-- Ensure string has a value.
IF LEN(@StringToFind) < 3
BEGIN
        RAISERROR('There should be at least one 
        character enclosed with percentage 
        symbols.', 16, 1)
        RETURN
END

-- Ensure string starts with a %.
IF SUBSTRING(@StringToFind, 1, 1) <> '%'
BEGIN
        RAISERROR('String should START with a 
        percentage symbol.', 16, 1)
        RETURN
END

-- Ensure string ends with a %.
IF SUBSTRING(@StringToFind, 
LEN(@StringToFind), 1) <> '%'
BEGIN
        RAISERROR('String should END with a 
        percentage symbol.', 16, 1)
        RETURN
END

-- Enumerate over all databases on the given server.
SET @SQL = 'EXEC sp_MSForEachDB ' + '''' 
+ 'USE [?]; PRINT ' + '''' + '''' +'?'  + '''' + '''' 
        + '; EXEC master.dbo.dba_SearchDB ' + ''''
+ '''' + @StringToFind  + '''' + '''' + ', [?];' + ''''

PRINT @SQL -- Useful for debugging.

-- Run the dynamic SQL.
EXECUTE sp_executesql @SQL
GO