MS SQL Server常用管理sql statement--发送邮件

利用SQL语句查询SQL中所有正在执行的命令
sysprocesses :存放有关在 Microsoft SQL Server 实例中运行的进程的信息,这些进程可以是客户端进程或系统进程。

查询语句select * from master..sysprocesses WHERE DB_NAME(dbid) = 'nscb'

查询出来的列的含义:

spid--SQL Server进程的ID。

kpid--Microsoft Windows线程的ID。

dbid--当前正由进程使用的数据库ID。

uid--执行命令的用户ID。如果用户数和角色数超过 32,767，则会溢出或返回 NULL。

cpu--进程的累计占用CPU的时间。

physical_io--进程的累计磁盘读取和写入。

memusage--当前分配给该进程的过程缓存中的页数。一个负数，表示进程正在释放由另一个进程分配的内存。

login_time--客户端进程登录到服务器的时间。对于系统进程，将存储 SQL Server的启动时间。

last_batch--客户端进程上次执行远程存储过程调用或EXECUTE语句的时间。对于系统进程，将存储 SQL Server的启动时间。

open_tran--进程的打开事务数。

status--进程ID的状态。例如，运行或者睡眠。

sid--用户的全局唯一标识符 (GUID)。

nt_username--进程的 Windows用户名(如果使用 Windows身份验证)或可信连接的 Windows用户名。

loginame 执行该进程的登录名。

这样，我们就可以查到所有执行命令的进程ID了，如果要查看该进程的具体的SQL语句，可以利用以下语句查看：

dbcc inputbuffer(进程号)

例如：

dbcc inputbuffer(56)

如果我们要终止(杀死)该进程，可利用以下语句：

kill进程号

例如：kill 70

kill连接某个数据库的所有进程:

select 'kill '+ cast(spid as varchar(10)) +';' from sysprocesses where dbid=db_id('数据库名');

查看死锁

sp_lock这个系统存储过程与SQL Server 2000打包在一起，它将使你对在你系统中发生的锁定有深入的了解。这个程序会从主数据库中的syslockinfo中返回与锁定相关的大量信息，而主数据库是一个包括了所有允许、转换和等待锁定请求信息的系统工作台。

EXECUTE sp_lock 

在我的系统中，这是该存储过程返回的内容。sp_lock返回的信息并不是一目了然的，要获得有用的数据，还需要做一些查找。但是，你也可以复制该存储过程的文本，然后创建一个新的，从而得到关于系统进程的更好的解释。（在这篇文章中，我们将集中讨论sp_lock返回的数据。）

从上面的结果我们可以看到spid、dbid、objid、indid、type、resource、mode和status字段。spid是进程标识号码，用于识别到SQL服务器的连接。要发现哪些用户和该spid相连，你就要执行存储过程sp_who(例sp_who 52)，并将spid作为一个参数传输给该程序。dbid是锁定发生的数据库，你可以在主数据库中的sysdatabases表格中找到它。字段objid用来显示在数据库中锁定发生所在的对象。要查看这个对象，你可以在主数据库中的sysobjects表格中查询指定的objid。

 在以上的屏幕截图中产生的单一记录并不一定能显示正在你的工作环境中发生的真实情况。在运行这个程序时，你想要找到500到1000个甚至更多结果。每一次你执行sp_lock，都将有可能得到不同的结果，因为又发生了新的锁定，而部分旧的锁定已经被解除了。如果你发现sp_lock返回的结果中，大量的结果都有着相同的spid，很有可能该进程正在进行大型的处理，同时这些锁定可能开始阻止新事务的发生。

 当你发现一个spid获得了大量的数据库锁定时，这将有助于确定什么存储过程或语句正在运行。为了达到这个目的，运行以下 DBCC命令：  

DBCC INPUTBUFFER(spid) 

这个DBCC命令将返回正在EventInfo字段中运行的语句的相关信息。

use   master
go
declare   @spid   int
--查询出死锁的SPID
select   @spid=blocked
from   (select   *   from   sysprocesses   where     blocked> 0   )   a  
where   not   exists(select   *   from   (select   *   from   sysprocesses   where     blocked> 0   )   b  
where   a.blocked=spid)

注释：比如有Blocked为63，其Spid为64；Spid为63的Blocked为0。那么我们可以得到结论：此时有阻塞发生，64北63阻塞（使用DBCC INPUTBUFFER(63)，可以获取引起阻塞的SQL语句）。如果Blocked等于其自己的Spid，说明这个会话正在做磁盘读写，它要等它自己的I/O做完。
print @spid
--输出引起死锁的操作
DBCC   INPUTBUFFER   (@spid)
--exec   sp_who 53
--kill 120

根据列名查询表名

Select * From sysobjects s  Where Exists( Select * From syscolumns  Where ID = s.ID AND name='OSMC_CODE')

查询错误信息

select * from master..sysmessages

查看日志使用情况

dbcc sqlperf(logspace)

清空日志方法 2005

USE [master]
GO
ALTER DATABASE ccflow SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE ccflow SET RECOVERY SIMPLE --简单模式
GO
USE ccflow
GO
DBCC SHRINKFILE (N'ccflow' , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE ccflow SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE ccflow SET RECOVERY FULL --还原为完全模式

数据库检查脚本

 --1.查看数据库版本信息
select @@version
--2.查看所有数据库名称及大小
select sp_helpdb
--3.查看数据库所在机器的操作系统参数
exec master..xp_msver
--4.查看数据库启动的参数
exec sp_configure
--5.查看数据库启动时间
select convert(varchar(30),login_time,120)
from master..sysprocesses where spid=1
--6.查看数据库服务器名
select 'Server Name:'+ltrim(@@servername)
--7.查看数据库实例名
select 'Instance:'+ltrim(@@servicename)
--8.数据库的磁盘空间呢使用信息
exec sp_spaceused
--9.日志文件大小及使用情况
dbcc sqlperf(logspace)
--10.表的磁盘空间使用信息
exec sp_spaceused 'tablename'
--11.获取磁盘读写情况
select
@@total_read [读取磁盘次数],
@@total_write [写入磁盘次数],
@@total_errors [磁盘写入错误数],
getdate() [当前时间]
--12.获取I/O工作情况
select @@io_busy,
@@timeticks [每个时钟周期对应的微秒数],
@@io_busy*@@timeticks [I/O操作毫秒数],
getdate() [当前时间]
--13.查看CPU活动及工作情况
select
@@cpu_busy,
@@timeticks [每个时钟周期对应的微秒数],
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],
getdate() [当前时间]
--14.检查锁与等待
exec sp_lock
--15.检查死锁
exec sp_who_lock --自己写个存储过程即可
/*
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
 循环开始
while @intCounter <= @intCountProperties
begin
取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
*/
--16.用户和进程信息
exec sp_who
exec sp_who2
--17.活动用户和进程的信息
exec sp_who 'active'
--18.查看进程中正在执行的SQL
dbcc inputbuffer(进程号)
exec sp_who3   
--19.查看所有数据库用户登录信息  
exec sp_helplogins 
--20.查看所有数据库用户所属的角色信息  
exec sp_helpsrvrolemember 
--21.查看链接服务器  
exec sp_helplinkedsrvlogin 
--22.查看远端数据库用户登录信息  
exec sp_helpremotelogin 
--23.获取网络数据包统计信息  
select  
@@pack_received [输入数据包数量],  
@@pack_sent [输出数据包数量],  
@@packet_errors [错误包数量],  
getdate() [当前时间]
--24.检查数据库中的所有对象的分配和机构完整性是否存在错误  
dbcc checkdb
--25.查询文件组和文件  
select  
df.[name],df.physical_name,df.[size],df.growth,  
f.[name][filegroup],f.is_default  
from sys.database_files df join sys.filegroups f  
on df.data_space_id = f.data_space_id 
--26.查看数据库中所有表的条数  
select  b.name as tablename ,  
a.rowcnt as datacount  
from    sysindexes a ,  
sysobjects b  
where   a.id = b.id  
and a.indid < 2  
and objectproperty(b.id, 'IsMSShipped') = 0
--27.得到最耗时的前10条T-SQL语句  
with maco as  
(  
select top 10  
plan_handle,  
sum(total_worker_time) as total_worker_time ,  
sum(execution_count) as execution_count ,  
count(1) as sql_count  
from sys.dm_exec_query_stats group by plan_handle  
order by sum(total_worker_time) desc  
)  
select  t.text ,  
a.total_worker_time ,  
a.execution_count ,  
a.sql_count  
from    maco a  
cross apply sys.dm_exec_sql_text(plan_handle) t   
--28. 查看SQL Server的实际内存占用  
select * from sysperfinfo where counter_name like '%Memory%' 
--29.显示所有数据库的日志空间信息  
dbcc sqlperf(logspace)
--30.收缩数据库  
dbcc shrinkdatabase(databaseName) 

--------------

1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同

3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
set @sql='select '+right(@list,len(@list)-1)+' from 表A'
exec (@sql)

4.查看硬盘分区:
EXEC master..xp_fixeddrives

5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
    =
   (select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'

6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
EXEC sp_msforeach_worker '?'

7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID  Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc

8.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'

9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0

10：获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')

11：查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

12：查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'

13：查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'

--获取连接SQL Server中的客户端IP地址

SELECT *FROM sys.dm_exec_connections

WHERE session_id = @@SPID

或用下面（对sql2008有效）

SELECT CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address

 CREATE TRIGGER trg_logon_attempttest

---记录登录用户名触发器

http://www.cnblogs.com/lyhabc/archive/2013/02/08/2909333.html
  CREATE TRIGGER trg_logon_attempttest
 ON ALL SERVER
 WITH EXECUTE AS'sa' 
 FOR LOGON,ALTER_LOGIN
 AS
 BEGIN
 DECLARE
     @cmd nvarchar(4000)
 
 SELECT
     @cmd = 'ECHO '
         + ORIGINAL_LOGIN()+ CHAR(9) + CONVERT(varchar(100), GETDATE(), 121)
         + ' >> d:\Logondata.txt'
 
 DECLARE @tb_re TABLE(re varchar(4000)); 
 INSERT @tb_re exec master.. xp_cmdshell @cmd
 END

------发送邮件

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'db_mail',         --配置文件名称
    @recipients = 'haichen@126.com.cn',    --收件email地址
    @subject = '你好',                 --邮件主题
    @body = 'hello'            --邮件正文内容