Предисловие
Нередко необходимо послать запрос всем БД (базам данных) всем указанным серверам. Многие DML-запросы можно сделать встроенными средствами. Например, так. Но как быть с DDL-запросами?
В данной статье будет разобран пример реализации приложения, которое отправляет запрос всем базам данных всех указанных серверов, используя MS SQL Server и C#.NET.
Решение
Например, необходимо создать или изменить представление (для примера возьмем создание представления из задачи):
Запрос
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
Это представление показывает размеры всех таблиц для каждой БД (базы данных).
Создадим файл FileQuery.sql и запишем туда указанный выше запрос. Теперь определим функцию, которая отправляет запрос из файла всем БД всем указанным серверам:
Функция
/// <summary>
/// Отправка запроса ко всем БД указанного сервера
/// </summary>
/// <param name="server">имя указанного сервера (экземпляра MS SQL Server)</param>
/// <param name="sql">T-SQL-запрос</param>
/// <param name="tw_log">Поток для логирования</param>
void RunQueryInAllDBServer(string server, string sql, TextWriter tw_log)
{
SqlConnectionStringBuilder scsb = null;
//список имен всех баз данных на сервере
List<string> ldbs = new List<string>();
//настройка строки подключения
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}");
}
}
}
}
Данная функция получает название экземпляра MS SQL Server, запрос T-SQL и поток для логирования. Внутри заполняется список имен всех БД сервера. Далее к каждой БД производится запрос. В целях безопасности выставлена авторизация Windows: scsb.IntegratedSecurity = true;
Также стоит быть осторожным с данной функцией, т к она выполняет любой запрос на всех БД сервера.
Теперь создадим код вызова описанной выше функции:
Код
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}");
}
}
Все. Приложение готово. Естественно, список серверов, а также путь к файлу запроса лучше вынести в настройки.
Результат
В данной статье был рассмотрен пример отправки запроса, который записан в файле, всем базам данных всех указанных серверов. Это позволяет посылать DDL-запросы ко всем БД, что нельзя сделать недокументированной хранимой процедурой sp_MSForEachDB.
А какими инструментами Вы пользуетесь для данной или подобной ей задачи?
Источники:
» MSDN
» Автоматизация по сбору данных о росте таблиц и файлов всех баз данных MS SQL Server
» Как применить операцию сразу ко всем таблицам или ко всем базам данных