Уважаемые Коллеги !'
+ N'Обращаем Ваше внимание, что запросы длительностью более ' + CONVERT(VARCHAR(8), CONVERT(DATETIME, @hh_alert_ms / 86400000.00), 114)
+ ' будут и впредь отслеживаться с направлением'
+ N'уведомлений. Запросы длительностью более ' + CONVERT(VARCHAR(8), CONVERT(DATETIME, @hh_kill_ms / 86400000.00), 114)
+ ' будут принудительно завершаться.
'
+ N' ' -- подвал тела email в формате HTML
DECLARE @body AS NVARCHAR(MAX) -- email-сообщение в формате HTML, собранное из заголовка, таблицы и подвала
IF @hh_alert_ms > @hh_kill_ms
SET @hh_kill_ms = @hh_alert_ms + 3600000
SELECT @except_command = @except_command + ',', @except_user = @except_user + ','
IF @debug_flag = 1
PRINT '10. Получаем список длительных процессов:' + CONVERT(VARCHAR(30), SYSDATETIME())
BEGIN TRY
DECLARE cursor_process CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
-- Выборка длительных процессов
SELECT process_id
,total_elapsed_time_ms
,user_mail
,body_td_mail = N'
' + CHAR(10)
FROM ( -- поля которые могут быть полезны, но в текущей реализации не используются, взяты в комментарии
SELECT process_id = sp.session_id -- id процесса
--,sp.start_time -- время старта процесса
,plan_handle = CONVERT(NVARCHAR(128), sp.plan_handle, 1) -- handle плана исполнения запроса
,total_elapsed_time_ms = sp.total_elapsed_time -- длительность выполнения процесса в миллисекундах
--,memusage_kb=s.memory_usage * 8
,batch_text = r.[text] -- полный sql-текст процесса
-- код sql выполняющийся в данный момент
--,sql_text = SUBSTRING(r.[text], sp.statement_start_offset/2+1 , (sp.statement_end_offset - sp.statement_start_offset)/2+1)
,hostname_from = s.[host_name] -- адрес хоста, с которого стратовал процесс
,[program_name] = s.[program_name] -- приложение, из которого запущен процесс
,full_username = s.login_name -- доменный логин пользователя
,user_mail = ISNULL(u.corporative_email,@admin_mail) -- если не удалось понять почту пользователя, то сообщение будет направляться администраторам
,user_mail_tbl = ISNULL(u.corporative_email,'--') -- email пользователя для отображения в таблице сообщения
,user_fio = ISNULL(u.FIO,'--')
FROM sys.dm_exec_requests sp
JOIN sys.dm_exec_sessions s ON sp.session_id = s.session_id
AND s.is_user_process = 1 -- не учитывать системные процессы
AND NOT s.session_id = @@SPID -- не учитывать процесс, запустивший запрос
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) r
----- Кастомизированный фрагмент получения из внутренних справочников реквизитов (ФИО, email) пользователя, инициировавшего длительный процесс
OUTER APPLY
(SELECT TOP 1 e.surname + ' '
+ ISNULL(LEFT(e.firstname,1),'-') + '.'
+ ISNULL(LEFT(e.middlename,1),'-') + '.' AS FIO
,e.corporative_email
FROM (SELECT DISTINCT ul.id_employee FROM dwh.dwh.user_login ul
WHERE ISNULL(ul.[login] = s.login_name) ul
JOIN dwh.dwh.employee e ON e.id_employee = ul.id_employee
-----
) u
WHERE sp.total_elapsed_time > @hh_alert_ms -- учитывать только длительные процессы
AND NOT ( @except_command LIKE '%' + sp.command + ',%' )
AND NOT ( @except_user LIKE '%' + s.login_name + ',%' )
) src
-- Курсор перебирет все процессы из предыдущего SELECTa: если процес подлежит удалению, то KILL его.
-- Здесь же в курсоре формируем таблицу со списком длительных процессов, которая будет включена в почтовое сообщение.
IF @debug_flag = 1
PRINT '20. Перебираем длительные процессы и формируем таблицу рассылки :' + CONVERT(VARCHAR(20), SYSDATETIME())
OPEN cursor_process
FETCH NEXT FROM cursor_process INTO @process_id, @total_elapsed_time_ms, @user_mail, @body_td_mail
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @subject_mail = -- Формируем тему сообщения
CASE WHEN @total_elapsed_time_ms BETWEEN @hh_alert_ms AND @hh_kill_ms
THEN 'Сервер ' + @@SERVERNAME + '. Предупреждение о длительном процессе'
WHEN @total_elapsed_time_ms > @hh_kill_ms
THEN 'Сервер ' + @@SERVERNAME + '. Уведомление о принудительном завершении длительного процесса'
ELSE 'Сервер ' + @@SERVERNAME + '. Уведомление о процессе'
END
-- удаление длительных процессов. Начало
IF @total_elapsed_time_ms > @hh_kill_ms
BEGIN TRY
SET @sql_cmd = 'KILL ' + CONVERT(VARCHAR(10), @process_id)
IF @debug_flag = 1
PRINT '30. Убиваем процессы:' + CONVERT(VARCHAR(30), SYSDATETIME()) + ' ' + @sql_cmd
EXECUTE ( @sql_cmd )
END TRY
BEGIN CATCH -- отлов ошибок удаления длительных процессов. Начало
-- если удаление процесса не получилось, то формируем сообщение для администратора и меняем тему на Предупреждение.
-- Сообщение о процесах, которые не удалость остановить, придёт администраторам для ручного разбора
IF @debug_flag = 1
PRINT '35. Произошла ошибка удаления процесса:' + CONVERT(VARCHAR(30), SYSDATETIME())
SET @subject_mail = 'Сервер ' + @@SERVERNAME + '. Предупреждение о длительном процессе'
-- Сообщение об ошибке KILL будет отправлено даже если флаг @mail_flag выключен
MERGE INTO @messages t -- формируем сообщение в таблицу @messages
USING (SELECT user_mail = @admin_mail -- подставляем администратора(ов)
-- Ошибку пишем в первую ячейку таблицы, остальные ячейки не меняем
,body_td_mail = REPLACE(@body_td_mail , '
Ошибка ' + ERROR_MESSAGE() + ' при выполнении :' + @sql_cmd + ' ' )
) s ON t.user_mail = s.user_mail AND t.subject_mail='Сервер ' + @@SERVERNAME + '. Ошибка остановки длительного процесса'
WHEN MATCHED THEN
UPDATE SET t.body_td_mail = t.body_td_mail + s.body_td_mail
WHEN NOT MATCHED THEN
INSERT (subject_mail, user_mail, body_td_mail)
VALUES ('Сервер '+@@SERVERNAME+'. Ошибка остановки длительного процесса', @user_mail, @body_td_mail);
END CATCH -- отлов ошибок удаления длительных процессов.Конец. @total_elapsed_time_ms > @hh_kill_ms
-- удаление длительных процессов.Конец
-- Формируем сообщения для отправки
IF @mail_flag=1 -- если флаг отправки почтовых сообщений включен
MERGE INTO @messages t
USING ( SELECT user_mail = @user_mail,body_td_mail = @body_td_mail ) s
ON t.user_mail = s.user_mail AND t.subject_mail = @subject_mail
WHEN MATCHED THEN
UPDATE SET T.body_td_mail = t.body_td_mail + s.body_td_mail
WHEN NOT MATCHED THEN
INSERT (subject_mail, user_mail, body_td_mail )
VALUES (@subject_mail, @user_mail, @body_td_mail);
FETCH NEXT FROM cursor_process INTO @process_id, @total_elapsed_time_ms, @user_mail, @body_td_mail
END -- WHILE @@FETCH_STATUS = 0
CLOSE cursor_process
DEALLOCATE cursor_process
-- Отправка email сообщений
-- Сообщения отправляются из сформированной на предыдущем шаге таблицы @messages
IF @debug_flag = 1
PRINT '40. Отправляем почтовое сообщение:' + CONVERT(VARCHAR(30), SYSDATETIME())
DECLARE cursor_mail CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
SELECT subject_mail, user_mail, body_td_mail FROM @messages
OPEN cursor_mail
FETCH NEXT FROM cursor_mail INTO @subject_mail, @user_mail, @body_td_mail
WHILE @@FETCH_STATUS = 0
BEGIN
-- для корректной работы необходимо настроить профиль учетной записи почтового сервера, в противном случае процессы будут KILL, но письма не будут отправляться
BEGIN TRY
SET @body = REPLACE(@body_head + @body_td_mail + @body_foot, CHAR(13), '')
SET @user_mail = REPLACE(@user_mail, ',', ';')
IF @copy_admin_all_mail = 1
SET @copy_recipients = REPLACE(@admin_mail, ',', ';')
ELSE
SET @copy_recipients = ''
IF @debug_flag = 1
BEGIN
PRINT 'To ' + @user_mail PRINT 'Copy ';
PRINT @copy_recipients;
PRINT @body;
END
EXEC msdb.dbo.sp_send_dbmail @profile_name=@profile_name
,@recipients=@user_mail
,@copy_recipients = @copy_recipients
,@body_format='HTML'
,@subject = @subject_mail
,@body = @body
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
FETCH NEXT FROM cursor_mail INTO @subject_mail, @user_mail, @body_td_mail
END -- WHILE @@FETCH_STATUS = 0
CLOSE cursor_mail
DEALLOCATE cursor_mail
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
SET @date_end = SYSDATETIME()
IF @debug_flag = 1
PRINT '100. Finish p_long_process_kill :' + CONVERT(VARCHAR(30), @date_end)
END
-- Запрос к серверу баз данных MS SQL для получения картины текущей активности и обнаружения проблемных запросов (подготовил Никоноров Вячеслав, г. Москва)
SELECT r.session_id -- id сессии
,r.[status] -- статус выполнения запроса
,s.nt_user_name AS NT_user_name -- доменный логин
,s.host_name -- имя компьютера, с которого запущен запрос
,USER_NAME(r.user_id) AS user_name
,db_name(r.database_id) AS DB -- имя базы данных
,r.blocking_session_id -- id сессии того, кого ждем, т.е. кто заблокировал наш запрос
,r.wait_type -- тип ожидания
,r.wait_time AS [wait_time_ms/1000]
,r.wait_resource
,r.reads -- количество физических чтений с жесткого диска
,r.logical_reads -- количество логических чтений, объем чтения страниц в памяти
,r.writes
,r.row_count
,r.start_time -- дата-время начала выполнения запроса
,r.cpu_time -- сферические "попугаи", показывающие использование CPU
,r.total_elapsed_time
,r.granted_query_memory AS granted_query_memory_by_page -- размер памяти в страницах, запрошенный для выполняемого участка кода
,m.requested_memory_kb/1024 AS 'Запрошенно памяти Мб'
,m.granted_memory_kb/1024 AS 'Уже предоставленно памяти Мб'
,m.required_memory_kb/1024 AS 'Мин требуемый объем памяти Мб'
,CASE WHEN m.is_next_candidate=0
THEN 'Нет'
WHEN m.is_next_candidate=1
THEN 'Да'
ELSE 'память уже есть'
END AS 'Следующий на получение памяти'
,r.percent_complete AS percent_for_alter_backup_etc -- значение актуально при создании резервных копий, усечении файлов
,r.estimated_completion_time
,r.scheduler_id
,s.program_name
,CASE r.statement_end_offset
WHEN -1
THEN NULL
ELSE object_name(t.objectid, t.dbid)
END AS object_name
,r.command
,CASE r.statement_end_offset
WHEN -1
THEN t.text
ELSE SUBSTRING(t.text, r.statement_start_offset/2,
(r.statement_end_offset/2)
- (r.statement_start_offset/2))
END AS SQL_text -- выполняющийся в текущий момент код (часть batch, процедуры, функции)
,p.query_plan -- план выполнения текущего кода
--,r.plan_handle
,r.user_id
,(r.estimated_completion_time/1000)/60 AS minute
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id=s.session_id
LEFT JOIN sys.dm_exec_query_memory_grants m ON r.session_id=m.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p -- этому обращению необходима блокировка схемы, поэтому весь запрос может не выполниться при наличии заблокированных объектов
WHERE r.[status] <> 'background'
AND r.command <> 'task manager'
AND r.session_id <> @@SPID
AND r.session_id > 50
-- AND r.database_id <> db_id('msdb')
ORDER BY r.logical_reads DESC
-- EXEC sp_whoisactive
-- Просмотр актуальности статистики по всем таблицам базы данных
SELECT t.object_id AS table_id
,t.name AS table_name
,t.modify_date AS table_modify_date
--,s.stats_id
,s.name AS stats_name
,sp.last_updated AS stats_last_updated -- если дата очень древняя, то и статистика, скорее всего, устаревшая
,sp.rows AS stats_rows
,sp.rows_sampled AS stats_rows_sampled
,s.auto_created AS stats_auto_created
FROM sys.tables t
INNER JOIN sys.stats s ON t.object_id=s.object_id
CROSS APPLY sys.dm_db_stats_properties(t.object_id, s.stats_id) sp
ORDER BY sp.last_updated
-- EXEC sp_updatestats
-- UPDATE STATISTICS "table_name" -- обновление статистики по таблице
-- Перестроение всех индексов по всем таблицам
DECLARE @table_name VARCHAR(250), @sql NVARCHAR(500), @fill_factor INT, @sort_in_tempdb VARCHAR(3), @data_compression VARCHAR(20)
SET @fill_factor = 90 -- возможные значения: 0...100
-- величина в % - насколько должен быть заполнен конечный уровень каждой страницы индекса
SET @sort_in_tempdb = 'ON' -- возможные значения: ON | OFF
-- промежуточные результаты сортировки, используемые для построения индекса, хранятся в tempdb;
-- если tempdb находится на разных наборах дисков пользовательской базы данных,
-- это может уменьшить время, необходимое для создания индекса
SET @data_compression = 'PAGE' -- возможные значения: NONE | ROW | PAGE | а также COLUMNSTORE | COLUMNSTORE_ARCHIVE
-- режим сжатия данных индекса
DECLARE table_cursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + name AS TableName FROM sys.tables
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @table_name
+ ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fill_factor)
+ ', SORT_IN_TEMPDB = ' + @sort_in_tempdb
+ ', DATA_COMPRESSION = ' + @data_compression + ')'
EXEC (@sql)
PRINT @sql -- для контроля процесса выполнения
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
-- Запрос к серверу баз данных MS SQL для нахождения самых больших таблиц
SELECT g.groupname AS group_name
,schema_name(o.schema_id) AS scheme_name
,o.name AS table_name
,MAX(o.modify_date) AS table_modify_date
,SUM(p.reserved_page_count) * 8 AS reserved_pages
,SUM(p.used_page_count) * 8 AS used_pages
,SUM(CASE WHEN (p.index_id < 2)
THEN ( p.in_row_data_page_count
+ p.lob_used_page_count
+ p.row_overflow_used_page_count)
ELSE p.lob_used_page_count
+ p.row_overflow_used_page_count
END) AS pages
,SUM(CASE WHEN (p.index_id < 2)
THEN p.row_count
ELSE 0
END) AS rows_count
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects o ON p.object_id = o.object_id AND o.type = 'U'
INNER JOIN sys.partitions t ON p.partition_id = t.partition_id
INNER JOIN sys.sysindexes i ON p.object_id = i.id AND t.index_id = i.indid
INNER JOIN sys.sysfilegroups g ON i.groupid = g.groupid
GROUP BY o.name, g.groupname, schema_name(o.schema_id)
ORDER BY reserved_pages DESC
DECLARE @all_DB_backup BIT -- =1 признак создавать ли резервные копии (бэкапы) для всех пользовательских баз данных
DECLARE @DB_name VARCHAR(50) -- иначе - имя конкретной базы данных
DECLARE @path_for_backups VARCHAR(500) -- путь для размещения файлов резервных копий
DECLARE @backup_file_name VARCHAR(500) -- имя файла резервной копии
DECLARE @file_date VARCHAR(20) -- дата создания резервной копии включается в имя файла
-- удаление ранее созданных файлов резервных копий баз данных (если команда xp_cmdshell не включена изначально, её надо "активировать" заранее)
-- EXEC xp_cmdshell 'DEL E:\SQL_BackUp\*.* /q'
SET @all_DB_backup = 1
SET @DB_name = 'DataManagement'
SET @path_for_backups = 'E:\SQL_Backup\'
SET @file_date = CONVERT(VARCHAR(20),GETDATE(),112)+'__'+REPLACE(LEFT(CONVERT(VARCHAR(20),GETDATE(),114),5) , ':', '_')
IF @all_DB_backup = 1 -- если нужно создать резервные копии для всех пользовательских баз данных
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- все базы данных, кроме системных, и которые в данный момент онлайн
AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DB_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backup_file_name = @path_for_backups + @DB_name + '_' + @file_date + '.bak'
BACKUP DATABASE @DB_name TO DISK = @backup_file_name WITH COMPRESSION
FETCH NEXT FROM db_cursor INTO @DB_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
ELSE
BEGIN
SET @backup_file_name = @path_for_backups + @DB_name + '_' + @file_date + '.bak'
BACKUP DATABASE @DB_name TO DISK = @backup_file_name WITH COMPRESSION
END
-- копирование файлов резервных копий баз данных в папку по сетевому пути:
-- EXEC xp_cmdshell 'xcopy E:\SQL_BackUp\ /-y Z:\SQL_BackUps\'
DECLARE @d_start DATETIME
DECLARE @d_end DATETIME
SET @d_start='18000101'
SET @d_end= '22000101'
SET LANGUAGE Russian
SELECT r.date_
,CAST(YEAR(r.date_) AS VARCHAR(4))
+CASE WHEN DATEPART(ww, r.date_)<10
THEN '.0'
ELSE '.' END+ CAST(DATEPART(ww, r.date_) AS VARCHAR(2)) AS year_week
,CAST(YEAR(r.date_) AS VARCHAR(4))
+CASE WHEN MONTH(r.date_)<10
THEN '.0'
ELSE '.' END+ CAST(MONTH(r.date_) AS VARCHAR(2)) AS year_month
,CAST(YEAR(r.date_) AS VARCHAR(4))
+'.'+ CAST(DATEPART(qq, r.date_) AS VARCHAR(1)) +' кв.' AS year_quarter
,CAST(YEAR(r.date_) AS VARCHAR(4))
+'.'+ CAST(DATEPART(qq, r.date_) AS VARCHAR(1)) +' quarter' AS year_quarter_en
,CAST(YEAR(r.date_) AS VARCHAR(4))
+CASE WHEN DATEPART(mm, r.date_)>6
THEN '.2 п\г.'
ELSE '.1 п\г.' END AS year_semester
,YEAR(r.date_) AS year_
,CASE WHEN YEAR(r.date_) BETWEEN 2000 AND 2099 THEN 'XXI'
WHEN YEAR(r.date_) BETWEEN 1900 AND 1999 THEN 'XX'
WHEN YEAR(r.date_) BETWEEN 2100 AND 2199 THEN 'XXII'
WHEN YEAR(r.date_) BETWEEN 1800 AND 1899 THEN 'XIX'
END AS century
,CASE WHEN DATEPART(mm, r.date_)>6 THEN 2 ELSE 1 END AS semester
,DATEPART(qq, r.date_) AS quarter_
,MONTH(r.date_) AS month_
,DATENAME(mm, r.date_) AS month_name
,CASE MONTH(r.date_)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS month_name_en
,CASE MONTH(r.date_)
WHEN 1 THEN 'Січень'
WHEN 2 THEN 'Лютий'
WHEN 3 THEN 'Березень'
WHEN 4 THEN 'Квітень'
WHEN 5 THEN 'Травень'
WHEN 6 THEN 'Червень'
WHEN 7 THEN 'Липень'
WHEN 8 THEN 'Серпень'
WHEN 9 THEN 'Вересень'
WHEN 10 THEN 'Жовтень'
WHEN 11 THEN 'Листопад'
WHEN 12 THEN 'Грудень'
END AS month_name_ua
,CASE MONTH(r.date_)
WHEN 1 THEN 'Студзень'
WHEN 2 THEN 'Люты'
WHEN 3 THEN 'Сакавік'
WHEN 4 THEN 'Красавік'
WHEN 5 THEN 'Травень'
WHEN 6 THEN 'Чэрвень'
WHEN 7 THEN 'Ліпень'
WHEN 8 THEN 'Жнівень'
WHEN 9 THEN 'Верасень'
WHEN 10 THEN 'Кастрычнік'
WHEN 11 THEN 'Лістапад'
WHEN 12 THEN 'Снежань'
END AS month_name_by
,CASE MONTH(r.date_)
WHEN 1 THEN 'Янв'
WHEN 2 THEN 'Фев'
WHEN 3 THEN 'Мар'
WHEN 4 THEN 'Апр'
WHEN 5 THEN 'Май'
WHEN 6 THEN 'Июн'
WHEN 7 THEN 'Июл'
WHEN 8 THEN 'Авг'
WHEN 9 THEN 'Сен'
WHEN 10 THEN 'Окт'
WHEN 11 THEN 'Ноя'
WHEN 12 THEN 'Дек'
END AS month_name_short
,CASE MONTH(r.date_)
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END AS month_name_short_en
,CASE WHEN MONTH(r.date_) IN (9,10,11) THEN 'осень'
WHEN MONTH(r.date_) IN (6,7,8) THEN 'лето'
WHEN MONTH(r.date_) IN (3,4,5) THEN 'весна'
WHEN MONTH(r.date_) IN (12,1,2) THEN 'зима'
END AS season
,CASE WHEN MONTH(r.date_) IN (9,10,11) THEN 'autumn'
WHEN MONTH(r.date_) IN (6,7,8) THEN 'summer'
WHEN MONTH(r.date_) IN (3,4,5) THEN 'spring'
WHEN MONTH(r.date_) IN (12,1,2) THEN 'winter'
END AS season_en
,CASE WHEN MONTH(r.date_) IN (9,10,11) THEN 'осінь'
WHEN MONTH(r.date_) IN (6,7,8) THEN 'літо'
WHEN MONTH(r.date_) IN (3,4,5) THEN 'весна'
WHEN MONTH(r.date_) IN (12,1,2) THEN 'зима'
END AS season_ua
,CASE WHEN MONTH(r.date_) IN (9,10,11) THEN 'восень'
WHEN MONTH(r.date_) IN (6,7,8) THEN 'лета'
WHEN MONTH(r.date_) IN (3,4,5) THEN 'вясна'
WHEN MONTH(r.date_) IN (12,1,2) THEN 'зіма'
END AS season_by
,DATEPART(dd, r.date_) AS day_
,DATEPART(dy, r.date_) AS year_day
,CONVERT(VARCHAR(10), r.date_, 104) AS date_str
,DATEPART(dw, r.date_) AS week_day
,DATENAME(dw, r.date_) AS week_day_str
,CASE DATEPART(dw, r.date_)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END AS week_day_str_en
,CASE DATEPART(dw, r.date_)
WHEN 1 THEN 'понеділок'
WHEN 2 THEN 'вівторок'
WHEN 3 THEN 'середа'
WHEN 4 THEN 'четвер'
WHEN 5 THEN 'п’ятниця'
WHEN 6 THEN 'субота'
WHEN 7 THEN 'неділя'
END AS week_day_str_ua
,CASE DATEPART(dw, r.date_)
WHEN 1 THEN 'панядзелак'
WHEN 2 THEN 'аўторак'
WHEN 3 THEN 'серада'
WHEN 4 THEN 'чацвер'
WHEN 5 THEN 'пятніца'
WHEN 6 THEN 'субота'
WHEN 7 THEN 'нядзеля'
END AS week_day_str_by
,CASE DATEPART(dw, r.date_)
WHEN 1 THEN 'Пн'
WHEN 2 THEN 'Вт'
WHEN 3 THEN 'Ср'
WHEN 4 THEN 'Чт'
WHEN 5 THEN 'Пт'
WHEN 6 THEN 'Сб'
WHEN 7 THEN 'Вс'
END AS week_day_short
,CASE DATEPART(dw, r.date_)
WHEN '1' THEN 'Mo'
WHEN '2' THEN 'Tu'
WHEN '3' THEN 'We'
WHEN '4' THEN 'Th'
WHEN '5' THEN 'Fr'
WHEN '6' THEN 'Sa'
WHEN '7' THEN 'Su'
END AS week_day_short_en
,DATEPART(ww, r.date_) AS week_
,CASE WHEN DAY(r.date_)<8 THEN 1
WHEN DAY(r.date_) BETWEEN 8 AND 14 THEN 2
WHEN DAY(r.date_) BETWEEN 5 AND 21 THEN 3
WHEN DAY(r.date_) BETWEEN 22 AND 28 THEN 4
WHEN DAY(r.date_)>28 THEN 5
END AS month_week
,CASE WHEN DAY(r.date_)<11 THEN 1
WHEN DAY(r.date_) BETWEEN 11 AND 20 THEN 2
WHEN DAY(r.date_)>20 THEN 3
END AS month_decade
,CASE WHEN DAY(r.date_)=
DAY(DATEADD(dd, -1, DATEADD(mm, 1,
CAST(YEAR(r.date_) AS VARCHAR(4))
+CASE WHEN MONTH(r.date_)<10 THEN '0' ELSE '' END
+CAST(MONTH(r.date_) AS VARCHAR(2))+'01')))
THEN 'да'
ELSE ''
END AS month_last_day
,CASE WHEN DAY(r.date_)=31 AND MONTH(r.date_)=12
THEN 'да'
ELSE ''
END AS year_last_day
,DAY(DATEADD(dd, -1, DATEADD(mm, 1,
CAST(YEAR(r.date_) AS VARCHAR(4))
+CASE WHEN MONTH(r.date_)<10 THEN '0' ELSE '' END
+CAST(MONTH(r.date_) AS VARCHAR(2))+'01'))) AS days_in_month
,CASE WHEN YEAR(r.date_) IN (1700, 1800, 1900, 2100, 2200, 2300)
THEN 365
ELSE
DATEDIFF(dd, CAST(YEAR(r.date_) AS VARCHAR(4))+'0101',
CAST(YEAR(r.date_) AS VARCHAR(4))+'1231')+1
END AS days_in_year
,CASE WHEN DATEPART(dw, r.date_) IN (6,7) THEN 1 ELSE 0 END AS weekend
,CASE WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040321' AND '18040420' THEN '01 Овен'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040421' AND '18040520' THEN '02 Телец'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040521' AND '18040621' THEN '03 Близнецы'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040622' AND '18040722' THEN '04 Рак'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040723' AND '18040823' THEN '05 Лев'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040824' AND '18040923' THEN '06 Дева'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040924' AND '18041023' THEN '07 Весы'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041024' AND '18041122' THEN '08 Скорпион'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041123' AND '18041221' THEN '09 Стрелец'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041222' AND '18041231' THEN '10 Козерог'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040101' AND '18040120' THEN '10 Козерог'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040121' AND '18040220' THEN '11 Водолей'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040221' AND '18040320' THEN '12 Рыбы'
END AS zodiac_sign
,CASE WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040321' AND '18040420' THEN '01 Aries'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040421' AND '18040520' THEN '02 Taurus'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040521' AND '18040621' THEN '03 Gemini'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040622' AND '18040722' THEN '04 Cancer'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040723' AND '18040823' THEN '05 Leo'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040824' AND '18040923' THEN '06 Virgo'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040924' AND '18041023' THEN '07 Libra'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041024' AND '18041122' THEN '08 Scorpio'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041123' AND '18041221' THEN '09 Sagittarius'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18041222' AND '18041231' THEN '10 Capricorn'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040101' AND '18040120' THEN '10 Capricorn'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040121' AND '18040220' THEN '11 Aquarius'
WHEN CAST('1804'+SUBSTRING(CONVERT(VARCHAR(10), r.date_, 112),5,4) AS DATE) BETWEEN '18040221' AND '18040320' THEN '12 Pisces'
END AS zodiac_sign_lat
,CASE WHEN YEAR(r.date_)=1800 THEN '09 Обезьяна'
WHEN YEAR(r.date_)=1801 THEN '10 Петух'
WHEN YEAR(r.date_)=1802 THEN '11 Собака'
WHEN YEAR(r.date_)=1803 THEN '12 Свинья'
ELSE
CASE (YEAR(r.date_)-1804) - ((YEAR(r.date_)-1804)/12)*12+1
WHEN 1 THEN '01 Крыса'
WHEN 2 THEN '02 Бык'
WHEN 3 THEN '03 Тигр'
WHEN 4 THEN '04 Кролик'
WHEN 5 THEN '05 Дракон'
WHEN 6 THEN '06 Змея'
WHEN 7 THEN '07 Лошадь'
WHEN 8 THEN '08 Овца'
WHEN 9 THEN '09 Обезьяна'
WHEN 10 THEN '10 Петух'
WHEN 11 THEN '11 Собака'
WHEN 12 THEN '12 Свинья'
END
END AS chinese_horoscope
,CASE WHEN DAY(r.date_)<10 THEN '0' ELSE '' END
+CAST(DAY(r.date_) AS VARCHAR(2))+' '
+CASE MONTH(r.date_)
WHEN 1 THEN 'января'
WHEN 2 THEN 'февраля'
WHEN 3 THEN 'марта'
WHEN 4 THEN 'апреля'
WHEN 5 THEN 'мая'
WHEN 6 THEN 'июня'
WHEN 7 THEN 'июля'
WHEN 8 THEN 'августа'
WHEN 9 THEN 'сентября'
WHEN 10 THEN 'октября'
WHEN 11 THEN 'ноября'
WHEN 12 THEN 'декабря'
END
+' '+CAST(YEAR(r.date_) AS VARCHAR(4))+ 'г.' AS date_full_str
,CASE MONTH(r.date_)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END
+CASE WHEN DAY(r.date_)<10 THEN ' 0' ELSE ' ' END
+CAST(DAY(r.date_) AS VARCHAR(2))
+' '+CAST(YEAR(r.date_) AS VARCHAR(4)) AS date_full_str_en
FROM (
SELECT DATEADD(day, num, @d_start) AS date_
FROM (
SELECT z*100000+r*10000+a*1000+b*100+c*10+d num
FROM (SELECT 0 z UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) z
CROSS JOIN
(SELECT 0 r UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) r
CROSS JOIN
(SELECT 0 a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN
(SELECT 0 b UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
CROSS JOIN
(SELECT 0 d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
) a
WHERE DATEADD(day, num, @d_start)<=@d_end
) r
ORDER BY 1
SELECT k.*
,CASE WHEN year_ BETWEEN 2000 AND 2099 THEN 'XXI'
WHEN year_ BETWEEN 1900 AND 1999 THEN 'XX'
WHEN year_ BETWEEN 1800 AND 1899 THEN 'XIX'
END AS century
,CASE WHEN month_ IN (9,10,11) THEN 'осень'
WHEN month_ IN (6,7,8) THEN 'лето'
WHEN month_ IN (3,4,5) THEN 'весна'
WHEN month_ IN (12,1,2) THEN 'зима'
END AS season
,CASE WHEN month_ IN (9,10,11) THEN 'autumn'
WHEN month_ IN (6,7,8) THEN 'summer'
WHEN month_ IN (3,4,5) THEN 'spring'
WHEN month_ IN (12,1,2) THEN 'winter'
END AS season_en
FROM (
SELECT d.day_current AS date_
,TO_CHAR(d.day_current, 'yyyy') || '.' || TO_CHAR(d.day_current, 'ww') AS year_week
,TO_CHAR(d.day_current, 'yyyy.mm') AS year_month
,TO_CHAR(d.day_current, 'yyyy.q') || ' кв.' AS year_quarter
,TO_CHAR(d.day_current, 'yyyy.q') || ' quarter' AS year_quarter_en
,TO_CHAR(d.day_current, 'yyyy') || '.'
|| TO_CHAR(CASE WHEN TO_NUMBER(TO_CHAR(d.day_current, 'mm'))>6 THEN 2
ELSE 1
END) || ' п\г.' AS year_semester
,TO_NUMBER(TO_CHAR(d.day_current, 'yyyy')) AS year_
,CASE WHEN TO_NUMBER(TO_CHAR(d.day_current, 'mm'))>6 THEN 2 ELSE 1 END AS semester
,TO_NUMBER(TO_CHAR(d.day_current, 'q')) AS quarter_
,TO_NUMBER(TO_CHAR(d.day_current, 'mm')) AS month_
,INITCAP(TO_CHAR(d.day_current, 'month', 'NLS_DATE_LANGUAGE=russian')) AS month_name
,INITCAP(TO_CHAR(d.day_current, 'month', 'NLS_DATE_LANGUAGE=english')) AS month_name_en
,INITCAP(TO_CHAR(d.day_current, 'mon', 'NLS_DATE_LANGUAGE=russian')) AS month_name_short
,INITCAP(TO_CHAR(d.day_current, 'mon', 'NLS_DATE_LANGUAGE=english')) AS month_name_short_en
,TO_NUMBER(TO_CHAR(d.day_current, 'dd')) AS day_
,TO_NUMBER(TO_CHAR(d.day_current, 'ddd')) AS year_day
,TO_CHAR(d.day_current, 'dd.mm.yyyy') AS date_str
,TO_CHAR(d.day_current, 'fmD') AS week_day
,TO_CHAR(d.day_current, 'fmDay', 'NLS_DATE_LANGUAGE=russian') AS week_day_str
,TO_CHAR(d.day_current, 'fmDay', 'NLS_DATE_LANGUAGE=english') AS week_day_str_en
,INITCAP(TO_CHAR(d.day_current, 'dy', 'NLS_DATE_LANGUAGE=russian')) AS week_day_short
,CASE TO_CHAR(d.day_current, 'fmD') WHEN '1' THEN 'Mo'
WHEN '2' THEN 'Tu'
WHEN '3' THEN 'We'
WHEN '4' THEN 'Th'
WHEN '5' THEN 'Fr'
WHEN '6' THEN 'Sa'
WHEN '7' THEN 'Su'
END AS week_day_short_en
,TO_NUMBER(TO_CHAR(d.day_current, 'ww')) AS week_
,TO_NUMBER(TO_CHAR(d.day_current, 'w')) AS month_week
,CASE WHEN d.day_current=LAST_DAY(d.day_current) THEN 1
ELSE 0
END AS month_last_day
,CASE WHEN TO_CHAR(d.day_current, 'fmD')='7'
OR
TO_CHAR(d.day_current, 'fmD')='6'
THEN 1
ELSE 0
END AS weekend
FROM (SELECT TO_DATE('31.12.1904','dd.mm.yyyy')+LEVEL day_current FROM dual CONNECT BY LEVEL <= 100000) d
WHERE d.day_current<=(SELECT TO_DATE('01.01.2060', 'dd.mm.yyyy') FROM dual) -- уточнить диапазон дат !
) k
SELECT n.nspname AS table_schema
,c.relname AS table_name
,a.attname AS column_name
,FORMAT_TYPE(COALESCE(NULLIF(t.typbasetype, 0), t.oid), COALESCE(NULLIF(t.typtypmod, -1), a.atttypmod)) AS data_type
,CASE WHEN a.attnotnul THEN 1 END AS mandatory
,CASE WHEN i.indisprimary THEN 1 END AS primary_key
,a.attnum AS column_number
,d.description AS column_comment
,s.description AS table_comment
,c.relkind AS table_kind
,c.relnatts AS table_columns_count
,c.relhaspkey AS table_has_pk
,c.relhasindex AS table_has_index
,c.relchecks AS table_checks_count
,c.reltuples AS table_rows_count
,c.reloptions AS table_options
,REPLACE(REPLACE(pg_catalog.pg_get_table_distributedby(c.oid), 'DISTRIBUTED BY (', ''), ')', '') AS distributed_by
,pg_catalog.pg_get_partition_def(c.oid) AS partition_def
FROM pg_attribute a
INNER JOIN pg_type t ON a.atttypid = t.oid
INNER JOIN pg_class c ON a.attrelid = c.oid
INNER JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_description d ON a.attrelid = d.objoid AND a.attnum = d.objsubid
LEFT JOIN pg_index i ON c.oid = i.indrelid AND a.attnum = ANY(i.indkey)
LEFT JOIN pg_description s ON c.oid = s.objoid AND s.objsubid = 0
WHERE a.attnum > 0 AND a.atttypid > 0
AND c.relname NOT LIKE 'r_%' AND c.relname NOT LIKE 'v_%' AND c.relname NOT LIKE 'fn_%'
AND n.nspname IN ('имяСхемы1', 'имяСхемы2')
/*
Скачиваем проект отсюда http://www.asstoredprocedures.codeplex.com ,
компилируем его, прикручиваем получившуюся dll к доступным сборкам SSAS-проекта.
Затем, в Calculation Script определяем нижеследующие вычисляемые меры:
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Дата обновления куба]
AS
ASSP.GetCubeLastProcessedDate()
,ASSOCIATED_MEASURE_GROUP = 'Ваша_группа_мер'
,DISPLAY_FOLDER = 'Системные'
,VISIBLE = 1
;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Версия SSAS]
AS
ASSP.discoverXmlMetaDataFullSingleValue("Version", "\Server|ID,Version")
,ASSOCIATED_MEASURE_GROUP = 'Ваша_группа_мер'
,DISPLAY_FOLDER = 'Системные'
,VISIBLE = 1
;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Размер OLAP-базы Мб]
AS
CInt(ASSP.discoverXmlMetaDataSingleValue("EstimatedSize", "\Database")/1000000)
,ASSOCIATED_MEASURE_GROUP = 'Ваша_группа_мер'
,DISPLAY_FOLDER = 'Системные'
,VISIBLE = 1
;
-------------------------------------------------------------------------------------------------------
/*
Также будет полезно:
по умолчанию назначается виртуальная мера-константа для того, чтобы при интерактивном использовании куба
при набрасывании измерений по осям строк/столбцов НЕ включалась в работу физическая мера куба
(особенно важно для ProClarity)
*/
CREATE MEMBER CURRENTCUBE.[MEASURES].VirtualDefaultMember AS 1, VISIBLE = 0 ;
ALTER CUBE CURRENTCUBE UPDATE DIMENSION [MEASURES], DEFAULT_MEMBER = [MEASURES].VirtualDefaultMember ;
Когда-то приходилось создавать бизнес-приложения в связке MS SQL + MS Access + MS Excel. Разделение кода на клиентскую часть и библиотеку функций, попытка эмуляции объектов-классов.
Скачать библиотеку MS Access
|