学步园

一、基础语法

Use master
go
select *      
from sys.messages  
go

效果如图示：

二、对选择中文消息解释，并按要种程度排序

Use master
go
select *
      
from sys.messages m
where 
m.language_id=2052 
order by severity;
go

效果如图示：

三、把结果表字段名改成易理解的中文

Use master
go
select m.message_id as '消息ID',
       L.name as '语言',
       m.severity as '严重级别',
       m.text as '描述'
      
from sys.messages m,
     sys.syslanguages l
where 
m.language_id=2052 
and L.name='简体中文'
and severity<>0

order by severity;
go

效果如图示：

四、过滤一些不常用的异常消息存入“一次过滤”表

Use master
go
select m.message_id as '消息ID',
       L.name as '语言',
       m.severity as '严重级别',
       m.text as '描述'
   into 一次过滤   
from sys.messages m,
     sys.syslanguages l
where 

m.language_id=2052 
and L.name='简体中文'
and severity<>0
and text not like '%警告%'
and text not like '%页%'
and text not like '%XML%'
and text not like '%脚本%'
and text not like '%挂起%'
and text not like '%分区%'
and text not like '%服务器%'
and text not like '%调试%'
and text not like '%磁盘%'
and text not like '%传输%'
and text not like '%协议%'
and text not like '%版本%'
and text not like '%物理%'
and text not like '%文件组%'
and text not like '%编译%'
and text not like '%网络%'
and text not like '%接口%'

and text not like '%备份%'
and text not like '%索引%'
and text not like '%游标%'
and text not like '%master%'
and text not like '%tempd%'
and text not like '%model%'
and text not like '%msdb%'
and text not like '%DBCC%'
and text not like '%握手%'
and text not like '%镜像%'
and text not like '%硬件%'
and text not like '%服务%'
and text not like '%联系%'
and text not like '%日志%'
and text not like '%存储过程%'
and text not like '%触发器%'
and text not like '%模式%'


order by severity;
go







效果如图示： 

五、从“一次过滤”表中选择出常用的一些SQL查询异常消息存入表 “一次选择”：

 
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [消息ID]
      ,[语言]
      ,[严重级别]
      ,[描述]
  FROM [master].[dbo].[一次过滤]
  where 
[master].[dbo].[一次过滤].[描述] like '%select%'
or [master].[dbo].[一次过滤].[描述] like '%where%'
or [master].[dbo].[一次过滤].[描述] like '%group%'
or [master].[dbo].[一次过滤].[描述] like '%order%'
or [master].[dbo].[一次过滤].[描述] like '%alter%'
or [master].[dbo].[一次过滤].[描述] like '%delete%'
or [master].[dbo].[一次过滤].[描述] like '%update%'
or [master].[dbo].[一次过滤].[描述] like '%create%'
or [master].[dbo].[一次过滤].[描述] like '%drop%'
or [master].[dbo].[一次过滤].[描述] like '%insert%'
or [master].[dbo].[一次过滤].[描述] like '%set%'
or [master].[dbo].[一次过滤].[描述] like '%order%'
or [master].[dbo].[一次过滤].[描述] like '%alter%'
or [master].[dbo].[一次过滤].[描述] like '%delete%'
or [master].[dbo].[一次过滤].[描述] like '%update%'
or [master].[dbo].[一次过滤].[描述] like '%create%'
order by [严重级别]
  Use master
go


效果如图示：