sql语句总结

----查看数据库缓存的SQL
use master
declare @dbid int
Select @dbid = dbid from sysdatabases here name = 'wfp'--修改成数据库的名称
select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name(dbid) as DatabaseName,SQL
from syscacheobjects
where dbid=@dbid
order by dbid,useCounts desc,objtype

--删除整个数据库的计划缓存
DBCC FREEPROCCACHE

--删除某个数据库的计划缓存
USE master
DECLARE @dbid INT
SELECT @dbid=dbid FROM sysdatabases WHERE NAME = '表名'
DBCC FLUSHPROCINDB (@dbid)

--sql server 查询区分大小写 collate Chinese_PRC_CS_AS_WS
select * from test where [name]='A' collate Chinese_PRC_CS_AI
select * from test where ascii(name) = ascii('A')

/*
SQL的换行
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
*/
PRINT 'Test'+CHAR(13)+'Name'
PRINT 'Test'+CHAR(10)+'Age'
PRINT 'Test'+CHAR(9)+'Tel'

--查看内存状态
dbcc memorystatus

--查看哪个引起的阻塞，blk
EXEC sp_who active

--查看锁住了那个资源id，objid
EXEC sp_lock