sql server查询语句执行时间 sqlserver查看执行过的语句

一、查询sql执行过的语句

SELECT TOP 1000 
       ST.text AS '执行的SQL语句',
       QS.execution_count AS '执行次数',
       QS.total_elapsed_time AS '耗时',
       QS.total_logical_reads AS '逻辑读取次数',
       QS.total_logical_writes AS '逻辑写入次数',
       QS.total_physical_reads AS '物理读取次数',       
       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 '2015-08-01 00:00:00' AND '2015-09-02 11:00:00' 
ORDER BY
     QS.total_elapsed_time DESC

二、删除所有数据表

USE [数据库名]
DECLARE @tableName VARCHAR(Max)
SET @tableName = ''
SELECT @tableName = @tableName + NAME + ','
FROM   sysobjects
WHERE  xtype = 'U';
SELECT @tableName = 'drop table ' + LEFT(@tableName, (LEN(@tableName) -1))
EXEC (@tableName)

三、删除所有存储过程

USE [数据库名]
DECLARE @procedureName VARCHAR(Max)
SET @procedureName = ''
SELECT @procedureName = @procedureName + NAME + ','
FROM   sysobjects
WHERE  xtype = 'P';  
SELECT @procedureName = 'DROP PROCEDURE ' + LEFT(@procedureName, (LEN(@procedureName) -1))
EXEC (@procedureName)

四、递归查询

1、查询下级所有数据

;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='Tom'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.ParentDept=h1.Department
)
select * from hgo

2、查询上级所有数据

;with hgo as
(
   select *,0 as rank from #EnterPrise where DepartManage='GiGi'
   union all
   select h.*,h1.rank+1 from #EnterPrise h join hgo h1 on h.Department=h1.ParentDept
)
select * from hgo

3、查询结果以逗号分割

;with cte as
                    (
                    select Name,EnName,ID,Pid from Whir_Cmn_Area where ID=1
                    union all
                    select x.Name,x.EnName,x.ID,x.Pid from Whir_Cmn_Area x,cte a
                    where x.pid=a.id
                    )
( SELECT STUFF( ( SELECT  ','+ CONVERT(nvarchar(max),ID)   FROM cte  FOR XML PATH('') ), 1, 0, '')  AS IDs )