Получение статистической информации о базе данных SQL Server. Общее число таблиц и записей

Для решения некоторых задач администрирования и разработки баз данных SQL Server может потребоваться статистическая информация о них. В частности, количество таблиц и записей в них.

Рассмотрим алгоритм получения общего количества таблиц в базе данных и суммарного количества записей.

Для того чтобы получить количество таблиц достаточно запросит количество строк в системном представлении sys.Tables, которое содержит информацию о таблицах в данной конкретной базе данных.

DECLARE @tablesTotalCount AS int;
SELECT @tablesTotalCount= count(*) FROM sys.tables;

Однако данное системное представление не отображает количество записей в таблице. Поэтому для того чтобы получить общее количество записей необходимо запросить количество записей в каждой отдельной таблице и суммировать.

Для решения этой задачи воспользуемся курсором. При обходе курсора будем формировать динамический SQL запрос, который получает количество данных в той или иной конкретной таблице, а полученные результаты суммировать.

Вначале объявим вспомогательные переменные:

--Общее число записей в БД
DECLARE @recordsTotalCount AS int;
--Число записей в таблице
DECLARE @recordsCount AS int;
--Название таблицы
DECLARE @tn AS nvarchar(max);
--Вспомогательная переменная для динамического SQL запроса
DECLARE @sql AS nvarchar(max);

Далее создадим курсор и присвоим начальные значения переменным @recordsTotalCount, @recordsCount начальные значения и получим из него имя первой таблицы базы данных.

DECLARE curStat CURSOR FOR SELECT name FROM sys.tables;
OPEN curStat;
SET @recordsCount=0;
SET @recordsTotalCount=0;
FETCH curStat INTO @tn;

После этого можно выполнить непосредственно сам обход курсора.

WHILE @@FETCH_STATUS=0
BEGIN
--Формируем динамический SQL запрос
   SET @sql='SELECT @rc=COUNT(*) FROM '+ @tn;
--Выполняем динамический SQL запрос
   EXEC sp_executesql @sql,N'@rc int output',@rc= @recordsCount OUTPUT;
--Суммируем результаты
   SET @recordsTotalCount=@recordsTotalCount+@recordsCount;
--Получаем имя следующей таблицы
   FETCH curStat INTO @tn;
END;

Вспомогательная переменная @sql для формирования SQL запроса необходима потому, что системная хранимая процедура sp_executesql, которая собственно и выполняет его, не поддерживает конкатенацию в параметрах.

После завершения обхода курсора закрываем его и освобождаем ресурсы.

CLOSE curStat;
DEALLOCATE curStat;

Результаты можно вывести с помощью обычного оператора SELECT.

SELECT @tablesTotalCount AS CountOfTables,@recordsTotalCount AS CountOfRecords;

Ниже приведён возможный пример реализации получения общего количества таблиц и записей в них при помощи хранимой процедуры.

CREATE PROCEDURE GetDataBaseStats
AS
BEGIN
   SET NOCOUNT ON;
--Получаем общее количество таблиц
   DECLARE @tablesTotalCount AS int;
   SELECT @tablesTotalCount= count(*) FROM sys.tables;
   --Общее число записей в БД
   DECLARE @recordsTotalCount AS int;
   --Число записей в таблице
   DECLARE @recordsCount AS int;
--Название таблицы
   DECLARE @tn AS nvarchar(max);
   --Вспомогательная переменная для динамического SQL запроса
   DECLARE @sql AS nvarchar(max);
   --Курсор для обхода таблиц
   DECLARE curStat CURSOR FOR SELECT name FROM sys.tables;
   OPEN curStat;
   SET @recordsCount=0;
   SET @recordsTotalCount=0;
   FETCH curStat INTO @tn;
   WHILE @@FETCH_STATUS=0
   BEGIN
     --Формируем динамический SQL запрос
    SET @sql='SELECT @rc=COUNT(*) FROM '+ @tn;
     --Выполняем динамический SQL запрос
     EXEC sp_executesql @sql,N'@rc int output',@rc= @recordsCount OUTPUT;
    --Суммируем результаты
     SET @recordsTotalCount=@recordsTotalCount+@recordsCount;
--Получаем имя следующей таблицы
     FETCH curStat INTO @tn;
   END;
   CLOSE curStat;
   DEALLOCATE curStat;
   SELECT @tablesTotalCount AS CountOfTables,@recordsTotalCount AS CountOfRecords;
END
GO

Допустим, имеется некоторая база данных состоящая из трёх таблиц.

  • Первая таблица пустая (0 записей);
  • Вторая таблица содержит 11 записей;
  • Третья таблица содержит 4 записи.

Если для этой базы данных выполнить вышеприведённую хранимую процедуру.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *