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

存储架构 2017-11-15


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

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.


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


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 (
   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 = t.[object_id] AND i1.indid < 2
 LEFT OUTER JOIN sysindexes as i2 ON = 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
  @@Servername AS Server,
  DB_NAME() AS DBName,
  SCHEMA_NAME(t.[schema_id]) as SchemaName,
  OBJECT_NAME(t.[object_id]) as TableName,
  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]

This view displays sizes of all
tables for each database.

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


        /// Sending a query to all databases of the specified server
        /// the name of the specified server (MS SQL Server instance)
        /// T-SQL-query
        /// flow for logging
        void RunQueryInAllDBServer(string server, string sql, TextWriter tw_log)
            SqlConnectionStringBuilder scsb = null;
            //the list of all database names on the server
            List ldbs = new List();
            //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;
                var result = comm.ExecuteReader();
                while (result.Read())
            //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;
                        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
MS SQL Server instance,
T-SQL query and
flow for logging. There, the list of names of all server databases is filled. Next,
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


In this article, we analyzed the example of sending the query stored in the file to all
databases of all specified servers.
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


责编内容by:codingsight (源链)。感谢您的支持!