SQL Server資料庫稽核 — 對象資訊抓取指令碼

    隨著資料庫的日積月累，而且經曆的開發人員是一茬又一茬，風格迥異，導致現在資料庫裡面的對象混亂，為了順應IT整合的趨勢，提高系統和資料庫的可維護性和易擴張性，決定將系統、資料庫以及伺服器的整合進

行到底，現在只是個開始。

   下面是剛寫的一個指令碼，抓取表、預存程序、觸發器和視圖等對象的資訊並匯總，後續對該部分資料進行提取分析，把影響效能的大資料表進行週期性備份和歸檔，對長期不再使用的對象，如表、儲存等進行備份後刪

除；同時對資料庫進行一些策略上的設定，以限定開發規範，讓資料庫煥然一新。

View Code

 1 USE [msdb] 2 GO 3 /****** Object:  StoredProcedure [dbo].[sp_DBMaintain_incrementData_statistic]    Script Date: 09/14/2012 18:10:56 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8  -- ========================================================================== 9 -- Author:    xxxx10 -- Create date:  2012/09/1411 -- Description:    統計使用者表、預存程序、函數、試圖和觸發器等對象變化、匯總12 -- ===========================================================================13 ALTER  procedure  [dbo].[sp_DBMaintain_incrementData_statistic]  14 as   15 SET NOCOUNT on 16  17 BEGIN TRY 18     declare @EXCEPTION VARCHAR(MAX)19     declare @MailSubject NVARCHAR(255)20 21     EXEC sp_MSforeachdb @command1= 'INSERT INTO msdb.dbo.Audit_Statistic22     SELECT DISTINCT ''CQ-SDS01'' AS ServerName,''?''AS DatabaseName,o.name AS ObjectName, o.id as ObjectID, a.type_desc AS ObjectType,i.rowcnt AS [RowCount],a.create_date AS CreateDate,a.modify_date AS ModiftDate,GETDATE() AS RecordDate23     FROM [?].dbo.sysobjects o 24     INNER JOIN [?].dbo.sysindexes i ON i.id=o.id AND i.indid IN(0,1) AND o.xtype IN (''U'') AND o.name <> ''sysdiagrams''25     INNER JOIN [?].sys.all_objects a ON  o.id=a.OBJECT_ID AND o.name=a.name  26     ORDER BY i.rowcnt DESC'27 28     EXEC sp_MSforeachdb @command1= 'INSERT INTO msdb.dbo.Audit_Statistic29     SELECT DISTINCT ''CQ-SDS01'' AS ServerName,''?'' AS DatabaseName,o.name AS ObjectName, o.id as ObjectID, a.type_desc AS ObjectType,0 AS [RowCount],a.create_date AS CreateDate,a.modify_date AS ModiftDate,GETDATE() AS RecordDate30     FROM  [?].dbo.sysobjects o 31     INNER JOIN [?].sys.all_objects a ON  o.id=a.OBJECT_ID AND o.name=a.name AND o.xtype IN (''p'',''fn'',''v'',''tr'') 32     ORDER BY a.modify_date DESC'33     34     EXEC sp_MSforeachdb @command1= 'INSERT INTO msdb.dbo.Audit_Summary35     SELECT ServerName, DatabaseName, ObjectType,COUNT(ObjectType) AS ObjectCount,SUM([RowCount]) AS TotalCount,GETDATE() AS RecordDate FROM (36     SELECT ''CQ-SDS01'' AS ServerName,''?'' AS DatabaseName,o.name AS ObjectName, a.type_desc AS ObjectType,ISNULL(i.rowcnt,0) AS [RowCount]37     FROM [?].dbo.sysobjects o 38     LEFT JOIN [?].dbo.sysindexes i ON i.id=o.id AND i.indid IN(0,1) 39     INNER JOIN [?].sys.all_objects a ON  o.id=a.OBJECT_ID AND o.name=a.name AND o.xtype IN (''p'',''fn'',''v'',''tr'',''u'') )A40     GROUP BY ServerName,DatabaseName,ObjectType'41         42 END TRY43 BEGIN CATCH44     SET @EXCEPTION = ERROR_MESSAGE() 45 END CATCH46 47 IF @EXCEPTION<>''48 BEGIN 49 50     SET @MailSubject='DBMaintain：Database statistic failed from ' + @@SERVERNAME  51     EXEC msdb.dbo.sp_send_dbmail52     @profile_name = 'mail',    53     @recipients = 'xxxxx@xxxx.com',                 54     @body = @EXCEPTION, 55     @subject = @MailSubject 56     57 END58