Blog Archives
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