常用SQL语句

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

 

5.查询SQL耗时语句

　　需要在master中执行　

SELECT TOP 20

total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],

 qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],

 last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],

 SUBSTRING(qt.text,qs.statement_start_offset/2+1,

 (CASE WHEN qs.statement_end_offset = -1

 THEN DATALENGTH(qt.text)

 ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)

AS [使用CPU的语法], qt.text [完整语法],

dbname=db_name(qt.dbid),

object_name(qt.objectid,qt.dbid) ObjectName

FROM sys.dm_exec_query_stats qs WITH(nolock)

CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt

WHERE execution_count>1

ORDER BY max_worker_time DESC