1.查询所有表的数据量
SELECT A.NAME ,B.ROWS FROM sysobjects A JOIN sysindexes B
ON A.id = B.id WHERE A.xtype = 'U' AND B.indid IN(0,1)
ORDER BY B.ROWS DESC
2.清理数据库日志
USE [master]
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE
GO
USE SHSDBarCode
GO
DBCC SHRINKFILE (N'TEST _log' , 2048,TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE TEST SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE TEST SET RECOVERY FULL
GO
3.查询包含的数据文本
select *
from sysobjects o, syscomments s
where o.id = s.id
and text like '%Spend%'
and o.xtype = 'P'
4.查询数据库操作
SELECT
ST.text AS '执行的SQL语句',
QS.creation_time AS '执行时间' ,
QS.*
FROM sys.dm_exec_query_stats QS
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE QS.creation_time BETWEEN '2020-07-08 00:00:00' AND '2020-07-08 08:59:59'
and st.text like '%TEST1%'
ORDER BY
QS.total_elapsed_time DESC