USE [master]
GO
/****** Object: StoredProcedure [dbo].[rkur_gooddba_whd] Script Date: 08/18/2015 10:13:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruslan Kurianov
-- Create date: 2015-05-29
-- Description: Сбор информации о базах данных на сервере
-- Version: 0.1
-- =============================================
CREATE PROCEDURE [dbo].[rkur_gooddba_whd]
-- Add the parameters for the stored procedure here
@sn nvarchar(32)= 'xxx',
@dbn nvarchar(32)='rkur_dbastat',
@schn nvarchar(32)='dbo',
@tbn nvarchar(64)='gooddba_whd'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if @sn <> 'xxx'
BEGIN
declare @whd nvarchar(128)
set @whd='['+@sn+'].['+@dbn+'].['+@schn+'].['+@tbn+']'
IF OBJECT_ID('tempdb..#tmpGood_DBA_project') IS NOT NULL
DROP TABLE #tmpGood_DBA_project
-- get data
SELECT
@@SERVERNAME AS Server,
d.name AS DBName ,
d.recovery_model_Desc AS RecoveryModel ,
d.Compatibility_level AS CompatiblityLevel ,
d.create_date ,
d.state_desc,
d.database_id,
d.user_access_desc,
d.is_read_only,
d.is_in_standby,
d.is_published,
d.log_reuse_wait_desc,
m.physical_name AS file_physical_name,
m.type_desc as file_type_desc,
m.state_desc as file_state_desc,
m.size*8/1024 as file_size,
GETDATE() as createdate,
datediff(second,'1970-01-01 00:00:00',getutcdate()) as createdate_unix
,b.Backup_finish_date
,bmf.Physical_Device_name
INTO #tmpGood_DBA_project
FROM sys.databases d
JOIN sys.master_files m ON d.database_id = m.database_id
INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D'
INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
group by d.name
,b.Backup_finish_date
,bmf.Physical_Device_name
,d.recovery_model_Desc
,d.Compatibility_level
,d.create_date ,
d.state_desc,
d.database_id,
d.user_access_desc,
d.is_read_only,
d.is_in_standby,
d.is_published,
d.log_reuse_wait_desc,
m.physical_name,
m.type_desc ,
m.state_desc ,
m.size*8/1024
having b.Backup_finish_date > convert(nvarchar(8),(getdate()-3),112)
ORDER BY d.NAME,b.Backup_finish_date DESC;
-- inserting result to whd
exec('insert into'+@whd+ 'select * from #tmpGood_DBA_project')
END
ELSE
print 'введи параметры для запуска хранимой процедуры'
END
GO
Similar Posts: