SQL Server 常用维护脚本 (1)

茅屋为秋风

　　/* SQL Server 常用维护脚本 */
　　--维护索引
　　CREATE PROCEDURE usp_MaintainIndex
　　@DBName NVARCHAR(20) = NULL
　　AS
　　BEGIN
　　SET NOCOUNT ON;
　　DECLARE @objectid int;
　　DECLARE @indexid int;
　　DECLARE @partitioncount bigint;
　　DECLARE @schemaname sysname;
　　DECLARE @objectname sysname;
　　DECLARE @indexname sysname;
　　DECLARE @partitionnum bigint;
　　DECLARE @partitions bigint;
　　DECLARE @frag float;
　　DECLARE @command varchar(8000);
　　--DECLARE@dbId int;
　　IF @DBName = '' OR @DBName = NULL
　　BEGIN
　　PRINT N'请指定需要整理的数据库名称....'
　　RETURN
　　END
　　-- ensure thetemporary table does not exist
　　IF EXISTS(SELECT name FROM sys.objects WHERE name = 'Work_To_Do_DefragIndex')
　　DROP TABLE Work_To_Do_DefragIndex;

　　--conditionally select from the function, converting object and index>　　--set@dbId=DB_ID();
　　SELECT
　　object_id AS objectid,
　　index_id AS indexid,
　　partition_number AS partitionnum,
　　avg_fragmentation_in_percent AS frag
　　--INTOWork_To_Do_DefragIndex FROM sys.dm_db_index_physical_stats (@dbId, NULL, NULL ,NULL, 'LIMITED')
　　INTO Work_To_Do_DefragIndex FROM sys.dm_db_index_physical_stats(DB_ID(@DBName), NULL, NULL , NULL, 'LIMITED')
　　WHERE avg_fragmentation_in_percent> 10.0 AND index_id > 0;
　　-- Declare thecursor for the list of partitions to be processed.
　　DECLARE partitions CURSOR FOR SELECT * FROM Work_To_Do_DefragIndex;
　　-- Open thecursor.
　　OPEN partitions;
　　-- Loopthrough the partitions.
　　FETCH NEXT
　　FROM partitions
　　INTO @objectid, @indexid, @partitionnum, @frag;
　　WHILE @@FETCH_STATUS = 0
　　BEGIN;
　　SELECT @objectname = o.name, @schemaname = s.name
　　FROM sys.objects AS o
　　JOIN sys.schemas as s ON s.schema_id = o.schema_id
　　WHERE o.object_id = @objectid;
　　SELECT @indexname = name
　　FROM sys.indexes
　　WHERE object_id = @objectid AND index_id = @indexid;
　　SELECT @partitioncount = count(*)
　　FROM sys.partitions
　　WHERE object_id = @objectid AND index_id = @indexid;
　　-- 30 is anarbitrary decision point at which to switch between reorganizing and rebuilding
　　IF @frag < 30.0
　　BEGIN;
　　SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE';
　　IF @partitioncount > 1
　　SELECT @command = @command + ' PARTITION=' + CONVERT(CHAR, @partitionnum);
　　EXEC (@command);
　　END;
　　IF @frag >= 30.0
　　BEGIN;
　　SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD';
　　IF @partitioncount > 1
　　SELECT @command = @command + ' PARTITION=' + CONVERT(CHAR, @partitionnum);
　　EXEC (@command);
　　END;
　　PRINT 'Executed ' + @command;
　　FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
　　END;
　　-- Close anddeallocate the cursor.
　　CLOSE partitions;
　　DEALLOCATE partitions;
　　-- drop thetemporary table
　　IF EXISTS(SELECT name FROM sys.objects WHERE name = 'Work_To_Do_DefragIndex')
　　DROP TABLE work_to_do
　　PRINT N'成功完成对数据库' + @DBName+ ' 的索引维护....'
　　END
　　GO
　　--另一种实例
　　Use DB
　　Go
　　Create Procedure SysOptIndexFrag
　　As
　　Set NoCount On
　　--按每个表索引中最大碎片率判断，且最大碎片率大于%
　　Select S.Name+'.'+T.Name as TabName,F.AvgFragPer Into #T
　　From Sys.Tables T Join
　　(Select Object_ID,Max(Avg_Fragmentation_in_Percent) as AvgFragPer
　　From sys.dm_db_index_physical_stats(DB_ID(),null,null,null,null)
　　Group By Object_ID) F On T.Object_ID=F.Object_ID
　　Join Sys.Schemas S On T.Schema_ID=S.Schema_ID
　　Where F.AvgFragPer>20
　　Order By F.AvgFragPer Desc
　　Declare @Tab Varchar(60),@S Varchar(800),@AvgFragPer Int
　　--以游标方式遍历找出的目标表，重组或重建索引
　　Declare Cur Cursor For Select TabName,AvgFragPer From #T
　　Open Cur
　　Fetch Next From Cur Into @Tab,@AvgFragPer
　　While @@Fetch_Status=0
　　Begin
　　--碎片率大于%，在线重建。否则重组
　　--注意，空间索引、XML索引，不支持在线重建会
　　If @AvgFragPer>30
　　Set @S='Alter Index All On '+@Tab+' REBUILD With (Online=On)'
　　Else
　　Set @S='Alter Index All On '+@Tab+' REORGANIZE'
　　Exec(@S)
　　Fetch Next From Cur Into @Tab,@AvgFragPer
　　End
　　Close Cur
　　DEAllocate Cur
　　--自定义取对象(SP、Trigger、Function) 源代码
　　--替换sp_helptext
　　USE Sfis_Data
　　GO
　　CREATE Procedure GetSPText
　　@Obj Varchar(200)=''
　　As
　　Declare @SVarchar(Max)
　　Declare @IInt
　　Declare @S2Varchar(8000)
　　Select @S=Definition From Sys.SQL_Modules Where Object_ID=Object_ID(@Obj)
　　--每次判断显示字节
　　While Len(@S)>2000
　　Begin
　　Set @I=CharIndex(Char(13),@S,2000) --取到字节后的第一个换行符位置
　　Set @S2=Left(@S,@I) --先截取这部分
　　Set @S=SubString(@S,@I+2,80000000) --将截取的部分舍弃，从位置@I+2
　　--是因为换行符是个字节，ASCII码回车换行NCHAR(13)+NCHAR(10)
　　Print @S2--显示这部分
　　End
　　Print @S--显示剩下的部分
　　Go
　　--自定义取对象索引
　　--替换sp_helpindex
　　Create Procedure GetIndex
　　@Tab Varchar(80)='',     --表名
　　@IsFrag Bit=0            --1：显示索引的碎片度、索引大小(较慢)，默认：不显示
　　As
　　Declare @Ind Table
　　(IndID TinyInt,
　　IndName Varchar(800),
　　IndCol Varchar(800),
　　InClude Varchar(800),
　　Filter Varchar(800),
　　AvgFrag TinyInt,

　　>　　Declare @S Varchar(400)
　　Declare @S2 Varchar(200)
　　Declare @S3 Varchar(80)
　　Declare @ID Int
　　Declare @IndID TinyInt
　　Select @ID=Object_ID(@Tab)
　　Insert @Ind(IndID,IndName,Filter,AvgFrag,SizeMB)
　　Select Index_ID,Name+Case When Type=1 Then ' CX' Else '' End
　　+Case When Is_Primary_Key=1 Then ' PK'
　　When Is_Unique=1 Then ' UQ' Else '' End as Name,

　　IsNull(SubString(Filter_Definition,2,Len(Filter_Definition)-2),'') as Filter,0 as AvgFrag,0 as>　　From Sys.Indexes Where Object_ID=@ID
　　Select I.Index_ID,I.Column_ID,C.Name,I.is_Included_Column Into#Cols
　　From Sys.Index_Columns I Join Sys.Columns C On I.[Object_ID]=C.[Object_ID] and I.Column_ID=C.Column_ID
　　Where I.Object_ID=@ID
　　If @IsFrag=1
　　Update I Set AvgFrag=S.AvgFrag,SizeMB=S.SizeMB
　　From @Ind I

　　Join(Select Index_ID,Cast(Avg_Fragmentation_In_Percentas TinyInt) as AvgFrag,Cast(Page_Count/128.0 as Int) as>　　From sys.dm_db_index_physical_stats(DB_ID(),@ID,null,null,null)) S On I.IndID=S.Index_ID
　　Declare Ind Cursor For Select IndID From @Ind
　　Open Ind
　　Fetch Next From Ind Into @IndID
　　While @@Fetch_Status=0
　　Begin
　　Set @S2=''
　　Set @S3=''
　　Select @S2=@S2+Case When Is_Included_Column=0 Then Name+',' Else '' End,
　　@S3=@S3+Case When Is_Included_Column=1 Then Name+',' Else '' End
　　From #ColsWhere Index_ID=@IndID
　　Update @IndSet IndCol=Case When @S2>'' Then Left(@S2,Len(@S2)-1) Else @S2 End,
　　InClude=Case When @S3>'' Then Left(@S3,Len(@S3)-1) Else '' End
　　Where IndID=@IndID
　　Fetch NextFrom Ind Into @IndID
　　End
　　Close Ind
　　DeAllocate Ind
　　Select I.IndName,I.IndCol+Case When I.InClude>'' Then ' :'+I.InClude Else '' End+Case When I.Filter>'' Then ' ->'+Filter Else '' End as Cols,
　　U.User_Seeks,U.User_Scans,U.User_Lookups,I.AvgFrag,I.SizeMB
　　From @Ind I Left Join Sys.DM_DB_Index_Usage_StatsU
　　On Database_ID=DB_ID() and [Object_ID]=@ID and I.IndID=U.Index_ID
　　GO
　　--分批删除数据，比如有万数据，一次性删除，很可能会锁住数据表，并且会产生大良事务日记
　　--较安全的做法是，每次删除N 条数据，这样不会锁住表
　　Use LCWSfis
　　Go
　　Print N'开始执行时间: ' + CONVERT(NVARCHAR(20), GETDATE(), 120) + NCHAR(13) + NCHAR(10)
　　Delete Top (5000)
　　From dbo.T_scanjobdetail Where gdid=5
　　While @@RowCOunt>0
　　Delete Top (5000)
　　From dbo.T_scanjobdetail Where gdid=5
　　Print N'结束执行时间: ' + CONVERT(NVARCHAR(20), GETDATE(), 120)
　　--禁止修改数据表
　　CREATE TRIGGER Table_Monitor ON DATABASE
　　FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
　　AS
　　Declare @S Varchar(800)
　　Set @S=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
　　Print @S
　　Create Table dbo.Test2(ID Int Not Null) --创建表
　　Go
　　Alter Table dbo.Test2 Add Name Varchar(20) --添加字段
　　Go

　　Alter Table dbo.Test2>　　Go
　　Alter Table dbo.Test2 Add Constraint PK_Test2 Primary Key (ID) --添加主键
　　Go
　　Alter Table dbo.Test2 Drop Column Name --删除字段
　　Go
　　Drop Table dbo.Test2 --删除测试表
　　--监控表，若名为表，若名为表，若名为Test2、Test3
　　--则取消操作
　　Create TRIGGER Table_Monitor_Deny ONDATABASE
　　FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE
　　AS
　　Declare @S Varchar(800)
　　Set @S=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
　　Set @S=SubString(@S,CharIndex('Table',@S)+6,200) --截取关键词Table后面的字符串，以准备取表名
　　If CharIndex(' ',@S)>0
　　Set @S=SubString(@S,1,CharIndex(' ',@S)-1) --截取表名，若表名含有空格，则会误判
　　If CharIndex('.',@S)>0 --若含. 通常是方案名, 如dbo.Test2
　　Set @S=SubString(@S,CharIndex('.',@S)+1,200)
　　Set @S=Replace(@S,'[','') --表名可能以[]界定表示，如dbo.[Test2]
　　Set @S=Replace(@S,']','')
　　If CharIndex(@S,'Test2,Test3,')>0 --若为Test2、Test3，则取消操作
　　Begin
　　Set @S='表：'+@S+' 禁止任何DDL 操作'
　　RaisError(@S,10,1)
　　Rollback
　　End
　　--创建后，再创建表名为Test2（或已有表Test2，对其修改、删除表结构），就会返回错误
　　--删除、删除DDLDDL 触发器触发器
　　Drop Trigger Table_Monitor On Database
　　Drop Trigger Table_Monitor_Deny OnDatabase
　　--创建服务器级DDLDDL 触发器，此处以登录为例触发器，此处以登录为例触发器
　　Use Master
　　Go
　　--创建日志表
　　Create Table dbo.SysLog(_Date DateTime,Remark Varchar(800))
　　Go
　　--创建测试登录帐号
　　Create Login LoginTest With Password='qwe123!@#'
　　--创建测试用户
　　Create User LoginUser For Login LoginTest
　　--授予插入权限，否则以LoginTest登录时会报错
　　Grant Insert To LoginUser
　　Go
　　Create TRIGGER Logon_Monitor ON All SERVER
　　FOR LOGON
　　AS
　　Set NoCount On
　　Declare @S Varchar(800)
　　Select @S='登录时间：'+Convert(Char(19),Login_Time,121)+' 电脑名：'+Host_Name
　　+' 登录名：'+Login_Name+' SPID：'+Cast(@@SPID as Varchar(10))
　　From Sys.dm_exec_sessions Where session_id=@@SPID
　　Insert Master.dbo.SysLog(_Date,Remark) Values (GetDate(),@S)
　　--开启SSMS以SQL登录帐号LoginTest、sa或Windows管理员帐号登录SQL SERVER，再执行查询验证效果：
　　Select _Date,Remark From dbo.SysLog order By _Date Desc
　　--删除服务器级删除服务器级删除服务器级DDLDDL 触发器
　　Drop Trigger LogOn_Monitor On All Server