Sending a request to all databases of all specified servers using MS SQL Server and C # .NET as an example

  • Tutorial

Foreword


It is often necessary to send a request to all databases (databases) to all specified servers. Many DML queries can be made with built-in tools. For example, like that . But what about DDL queries?

In this article, an example implementation of an application that sends a request to all databases of all specified servers using MS SQL Server and C # .NET will be analyzed.

Decision


For example, you need to create or modify a view (for example, let's create a view from a task ):

Inquiry
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
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 shows the sizes of all tables for each database (database).

Create the file FileQuery.sql and write the above query there. Now we define a function that sends a request from a file to all databases to all specified servers:

Function
/// 
        /// Отправка запроса ко всем БД указанного сервера
        /// 
        /// имя указанного сервера (экземпляра MS SQL Server)
        /// T-SQL-запрос
        /// Поток для логирования
        void RunQueryInAllDBServer(string server, string sql, TextWriter tw_log)
        {
            SqlConnectionStringBuilder scsb = null;
            //список имен всех баз данных на сервере
            List ldbs = new List();
            //настройка строки подключения
            scsb = new SqlConnectionStringBuilder();
            scsb.ApplicationName = "НАЗВАНИЕ_ПРИЛОЖЕНИЯ";
            scsb.InitialCatalog = "master";
            scsb.IntegratedSecurity = true;
            scsb.DataSource = server;
            //вывод в лог текущего времени и названия экземпляра сервера MS SQL Server
            tw_log.WriteLine($"{DateTime.Now} ServerName: {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 на каждой БД сервера
            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]} успешно выполнен запрос");
                    }
                    catch(Exception exp)
                    {
                        tw_log.WriteLine($"{DateTime.Now} DBName: {ldbs[i]} Exception: {exp.Message}");
                    }
                }
            }
        }


This function receives the name of the instance of MS SQL Server, a T-SQL query, and a stream for logging. Inside, a list of names of all server databases is populated. Next, a request is made to each database. For security reasons, Windows authorization is set: scsb.IntegratedSecurity = true;

You should also be careful with this function, since it performs any request on all server databases.

Now create the call code for the function described above:

The code
string sql = null;
using (Stream st_log = new FileStream("НАЗВАНИЕ_ФАЙЛА_ДЛЯ_ЛОГИРОВАНИЯ", 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();
                        }
                    }
//здесь servers-массив имен экземпляров MS SQL Server, на которые необходимо отправить запрос T-SQL
                    for (int i = 0; i < servers.Length; i++)
                    {
                        RunQueryInAllDBServer(servers[i], sql, tw_log);
                    }
                    tw_log.WriteLine($"Конец {DateTime.Now}");
                }
            }


All. The application is ready. Naturally, the list of servers, as well as the path to the request file, is better to place in the settings.

Result


This article examined an example of sending a request, which is written in a file, to all databases of all the specified servers. This allows you to send DDL queries to all databases, which cannot be done with the sp_MSForEachDB undocumented stored procedure.

What tools do you use for this or a similar task?

Sources:


» MSDN
» Automation to collect data on the growth of tables and files of all MS SQL Server databases
» How to apply the operation to all tables or to all databases at once

Also popular now: