Для решения некоторых задач администрирования и разработки баз данных 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 записи.
Если для этой базы данных выполнить вышеприведённую хранимую процедуру.