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
D
DL-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 thisarticle:

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:

/// 
        /// 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;
 
                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.

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

codingsight稿源:codingsight (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录