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

Introduction

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.

Solution

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

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

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.