工作日记七 清除数据库的所有缓存

工作日志七 清除数据库的所有缓存

--查询数据库存储过程缓存
SELECT
      C.name AS object_name,
      C.object_id,
      B.text AS sql_text
  FROM sys.dm_exec_cached_plans A
  CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) B
  INNER JOIN sys.procedures C
      ON B.objectid = C.object_id
  WHERE A.objtype = 'Proc'
--查询数据库信息
select * from sys.databases
--清除数据库缓存
declare @dbname  varchar(10) ,
@dbid int
set @dbname = (select db_name())
print @dbname
set @dbid =(select db_id(@dbname) as db_id)
print @dbid
DBCC FREEPROCCACHE;--清空SQL缓存
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ( 'all' )
DBCC FlushProcInDB(@dbid)