Execute one query in all SQL Server databases at once

We come across situations like searching for a table in all the databases in one server, or executing any kind of query to all the databases. In-fact sometimes we want to write queries for all the databases with small twist.

For example, I wanted to search for a table name if it used in any View/StoreProc definition within any of the database before I delete the table.

Following query does the same:

DECLARE @cmd VARCHAR(1000)
	,@search_string VARCHAR(200)


SET @cmd = 'SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc 
			FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id 
			INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id 
			WHERE m.definition Like ''%' + @search_string + '%'''

-- Uncomment the following if you have problems with your command and want to see the command
-- PRINT @cmd


--Run for every database on the server
EXEC sp_msforeachdb @cmd

Above kind of query can help you with :

a. Find a table name in complete SQL Server Databases

b. Prepare complete query which will be run on all the databases

c. Update something in all the databases at once

d. Example used with Dynamic query can help achieve many ad-hoc work done on SQL Server