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

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:

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [inf].[vTableSize] as
with pagesizeKB as (
	SELECT low / 1024 as PageSizeKB
	FROM master.dbo.spt_values
	WHERE number = 1 AND type = 'E'
)
,f_size as (
	select p.[object_id], 
		   sum([total_pages]) as TotalPageSize,
		   sum([used_pages])  as UsedPageSize,
		   sum([data_pages])  as DataPageSize
	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
	left join sys.internal_tables it on p.object_id = it.object_id
	WHERE OBJECTPROPERTY(p.[object_id], N'IsUserTable') = 1
	group by p.[object_id]
)
,tbl as (
	SELECT
	  t.[schema_id],
	  t.[object_id],
	  i1.rowcnt as CountRows,
	  (COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0)) * (select top(1) PageSizeKB from pagesizeKB) as ReservedKB,
	  (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0)) * (select top(1) PageSizeKB from pagesizeKB) as DataKB,
	  ((COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))
	    - (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as IndexSizeKB,
	  ((COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0))
	    - (COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as UnusedKB
	FROM sys.tables as t
	LEFT OUTER JOIN sysindexes as i1 ON i1.id = t.[object_id] AND i1.indid < 2
	LEFT OUTER JOIN sysindexes as i2 ON i2.id = t.[object_id] AND i2.indid = 255
	WHERE OBJECTPROPERTY(t.[object_id], N'IsUserTable') = 1
	OR (OBJECTPROPERTY(t.[object_id], N'IsView') = 1 AND OBJECTPROPERTY(t.[object_id], N'IsIndexed') = 1)
	GROUP BY t.[schema_id], t.[object_id], i1.rowcnt
)
SELECT
  @@Servername AS Server,
  DB_NAME() AS DBName,
  SCHEMA_NAME(t.[schema_id]) as SchemaName,
  OBJECT_NAME(t.[object_id]) as TableName,
  t.CountRows,
  t.ReservedKB,
  t.DataKB,
  t.IndexSizeKB,
  t.UnusedKB,
  f.TotalPageSize*(select top(1) PageSizeKB from pagesizeKB) as TotalPageSizeKB,
  f.UsedPageSize*(select top(1) PageSizeKB from pagesizeKB) as UsedPageSizeKB,
  f.DataPageSize*(select top(1) PageSizeKB from pagesizeKB) as DataPageSizeKB
FROM f_size as f
inner join tbl as t on t.[object_id]=f.[object_id]

GO

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:

/// <summary>
        /// Sending a query to all databases of the specified server
        /// </summary>
        /// <param name="server">the name of the specified server (MS SQL Server instance)</param>
        /// <param name="sql">T-SQL-query</param>
        /// <param name="tw_log">flow for logging</param>
        void RunQueryInAllDBServer(string server, string sql, TextWriter tw_log)
        {
            SqlConnectionStringBuilder scsb = null;

            //the list of all database names on the server
            List<string> ldbs = new List<string>();

            //configuration of the connection string
            scsb = new SqlConnectionStringBuilder();
            scsb.ApplicationName = "APPLICATION_NAME";
            scsb.InitialCatalog = "master";
            scsb.IntegratedSecurity = true;
            scsb.DataSource = server;

            //output of the current time and instance name of MS SQL Server to the log 
            tw_log.WriteLine($"{DateTime.Now} ServerName: {server}:");

            //creating a connection with the query to receive all database names on the server
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = scsb.ConnectionString;

                SqlCommand comm = new SqlCommand("select [name] from sys.databases");
                comm.CommandType = System.Data.CommandType.Text;
                comm.Connection = conn;

                conn.Open();
                var result = comm.ExecuteReader();

                while (result.Read())
                {
                    ldbs.Add(result.GetString(0).ToString());
                }
            }

            //SQL query execution on each server database
            for (int i = 0; i < ldbs.Count; i++)
            {
                using (SqlConnection conn = new SqlConnection())
                {
                    scsb.InitialCatalog = ldbs[i];
                    conn.ConnectionString = scsb.ConnectionString;

                    SqlCommand comm = new SqlCommand(sql);
                    comm.CommandType = System.Data.CommandType.Text;
                    comm.Connection = conn;

                    conn.Open();
                    try
                    {
                        comm.ExecuteNonQuery();
                        tw_log.WriteLine($"{DateTime.Now} DBName: {ldbs[i]} the query is successful");
                    }
                    catch(Exception exp)
                    {
                        tw_log.WriteLine($"{DateTime.Now} DBName: {ldbs[i]} Exception: {exp.Message}");
                    }
                }
            }
        }

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:

string sql = null;
using (Stream st_log = new FileStream("NAME_OF_THE_FILE_FOR_LOGGING", FileMode.Create, FileAccess.Write))
            {
                using (TextWriter tw_log = new StreamWriter(st_log, Encoding.Default))
                {

                    using (Stream st = new FileStream("FileQuery.sql", FileMode.Open, FileAccess.Read))
                    {
                        using (TextReader tr = new StreamReader(st, Encoding.Default))
                        {
                            sql = tr.ReadToEnd();
                        }
                    }
//here servers is the array of MS SQL Server instance names, to which a T-SQL query should be sent
                    for (int i = 0; i < servers.Length; i++)
                    {
                        RunQueryInAllDBServer(servers[i], sql, tw_log);
                    }

                    tw_log.WriteLine($"Конец {DateTime.Now}");
                }
            }

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 tool:

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.