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

SQL Server 2017 – Data Tools installation

This post to third and final part of SQL Server 2017 setup guide. We have got

1. SQL Server 2017 installed
2. SQL Server Management Studio 2018
3. Adventure Works SQL Database Sample
4. Adventure Works Multidimensional and Tabular Cube Sample

Now final piece is Tool for development of SQL Server 2017 solutions. We need BIDS (Business Intelligence Development Studio) or call it SQL Server Data Tools. Installation is quite simple.

1. Download SSDT for Visual Studio 2017 (15.9.2)
2. At installation it will ask for two things
   • Visual Studio 2017 instance if not then SSDT with Visual Studio 2017
   • All MSBI services (Integration, Analysis and Reporting)
3. Installer will do all the things for setting up Business Intelligence development setup within Visual Studio 2017, make sure Internet is connected.

That’s all, you are done with development part as well. Happy Coding!!

Comment in case of any hiccups and compliments are always welcome.