SQL Server database Audit-script for capturing Object Information

As the database accumulates over time and experienced developers are different in different styles, the objects in the database are messy. In order to comply with the IT integration trend, improve the maintainability and scalability of the system and database, and decide to integrate the system, database and server

It's just the beginning.

The following is a newly written script that captures and summarizes information about objects such as tables, stored procedures, triggers, and views, and extracts and analyzes the data in the future, regularly back up and archive large data tables that affect performance, and back up and delete objects that are not used for a long time, such as tables and storage.

In addition, some policy settings are also made for the database to limit the development specifications, so that the database can be refreshed.

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: collect statistics on object changes, such as user tables, stored procedures, functions, attempts, and triggers. 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,. type_desc as objecttype, I. rowcnt as [rowcount],. create_date as createdate,. 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 =. object_id and O. name =. 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,. type_desc as objecttype, 0 as [rowcount],. create_date as createdate,. modify_date as modiftdate, getdate () as recorddate30 from [?]. DBO. sysobjects o 31 inner join [?]. SYS. all_objects A on O. id =. object_id and O. name =. name and O. xtype in (''p', ''fn '', ''v'', ''tr'') 32 order by. 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 =. object_id and O. name =. 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