adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Сбор информации о базах данных на сервере

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>