Скрипт MS SQL для вывода информации о дате последнего изменения БД и размере БД. Для всех БД на сервере.

Что делает:
Скрипт выводит в виде таблицы информацию о дате последнего изменения базы ( берется из _UsersWorkHistory) и её размер.
Кому нужно:
Может помочь администраторам у которых на сервере множество различных БД 1С.
Одним нажатием кнопки можно понять какие БД стоит рассматривать как «неиспользуемые».

Так же может использоваться в качестве примера пакетной работы с таблицами, и базами. 

Как работает:

Работает только с базами 8.2 управляемое приложение т.к. для определения даты последнего изменения используется системная таблица: _UsersWorkHistory

При желании можно заменит запрос для определения даты последнего изменения: «Insert into #T1 SELECT »’+ @dbname + »’ as f1, Max(_Date) as f2 FROM [‘+ @dbname+’].[dbo].[_UsersWorkHistory]» на ваш. 

Базы не 1С или 1С ниже 8.2 не обрабатываются. 

 

Скрипт: 

print ‘Получение информации о размерерах БД’
if object_id(‘tempdb..#T2’) is not null drop table #T2 —Проверим наличие временных таблиц и если существуют удалим.
—Создадим временную таблицу для хранения информации о размерах БД.
create table #T2 (name sysname, db_size nvarchar(13), owner sysname, dbid smallint, created nvarchar(11), status nvarchar(600), compatibility_level tinyint)

insert into #T2 EXEC sp_helpdb —Вызов системной функции и помещение результатов в временную таблицу T2

print ‘Получение информации о использовании БД’
if object_id(‘tempdb..#T1’) is not null drop table #T1 —Проверим наличие временных таблиц и если существуют удалим.

—Создадим временную таблицу T1 для хранения информации о последнем измении БД:
—Поля:
— F1 — Имя базы
— F2 — Дата последнего изменения.

CREATE TABLE #T1 (f1 sysname, f2 datetime);

— Выбираем список не системных (database_id>4), активных(state=0) БД
— Опции LOCAL STATIC READ_ONLY используются для ускорения работы(LOCAL,READ_ONLY) и для того что бы полученная таблица не изменялась в процессе работы(STATIC).
declare dbcursor CURSOR LOCAL STATIC READ_ONLY for SELECT name FROM sys.Databases where database_id>4 and state=0

declare @Count int —Счетчик баз по которым прошел курсор (Используется для отладки что бы видеть что курсор обошел все базы).

declare @dbname sysname —Содержит имя текущей базы данных, используется при работе с курсором.

declare @sqlQuery nvarchar(4000) —Используется для формирования текста запроса (хранит текст запроса).
declare @ParmDefinition nvarchar(500);
declare @CountRow int

OPEN dbcursor
FETCH NEXT FROM dbcursor into @dbname —Устанавливаем @dbname для первой строки набора.

set @count=0 —Устанавливаем начальное значение счетчика (исп. для отладки)

WHILE @@FETCH_STATUS = 0 —Начинаем цикл. @@FETCH_STATUS глобальная переменная для работы с текущим «CURSOR»

BEGIN
—Вывод отладочной информации в консоль.
set @count=@count+1
print @count
print @dbname

—Проверяем есть ли в текущей БД таблица: _UsersWorkHistory (в 8.2 в этой таблице хранится история работы пользователей) .
set @sqlQuery =’select @CountRowOUT = count(*) from [‘+@dbname+’].information_schema.tables where TABLE_NAME=»_UsersWorkHistory»’
set @ParmDefinition = ‘@CountRowOUT int OUTPUT’;
exec sp_executesql @sqlQuery, @ParmDefinition, @CountRowOUT=@CountRow OUTPUT

if @CountRow > 0 —Если результат запроса не пустой значит такая таблица есть.
Begin
set @sqlQuery =’Insert into #T1 SELECT »’+ @dbname + »’ as f1, Max(_Date) as f2 FROM [‘+ @dbname+’].[dbo].[_UsersWorkHistory]’
exec sp_executesql @sqlQuery
END
ELSE print ‘Необработано (8.1 или база шаблона): ‘+@dbname

FETCH NEXT FROM dbcursor into @dbname
END

CLOSE dbcursor
Deallocate dbcursor

SELECT #T1.*, #T2.*
FROM #T1 LEFT OUTER JOIN
#T2 ON #T1.F1 = #T2.name
order by f2,db_size