Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET

Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET
Rate this post

Often, it is necessary to send a query to all databases of all specified servers. Many DML-queries can be created with built-in tools. However, what about DDL-queries?

In this article, we are going to explore an example of implementing the application that sends a query to all databases of all specified servers, using MS SQL Server and C#.NET.

Solution

For example, it is necessary to create or modify a view. As an example, use the view from this article:

Query:

This view displays sizes of all the tables for each database.

Create the FileQuery.sql file and save the specified query to it. Now, define the function that sends a query from the file to all databases of all specified servers:

Function:

The function receives the name of the MS SQL Server instance, the T-SQL query and flow for logging. There, the list of names of all server databases is filled. Next, the query is executed against each database. For security purposes, Windows authorization is set: scsb.IntegratedSecurity = true;

Now, create a code for calling the above function:

That’s all. The application is ready. Of course, it is better to store the list of servers and a path to the file in the settings.

Summary

In this article, we analyzed the example of sending the query stored in the file to all the databases of all specified servers. This allows sending DDL-queries to all databases, which can not be done by the undocumented sp_MSForEachDB stored procedure.

What tools do you use to perform this task?

Also read:

How to automate data collection on the SQL Server database growth

 

Useful products:

dbForge Query Builder for SQL Server – allows users to build quickly and easily complex SQL queries via an intuitive visual interface without manual code writing.

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov