select top 1000 s2.dbid,
DB_NAME(s2.dbid) as [dbname],
(select top 1 substring(s2.text,statement_start_offset/2+1,
((case when statement_end_offset=-1
then( len(convert(nvarchar(max),s2.text))*2)
else statement_end_offset end)- statement_start_offset)/2+1)
)as sqll,
last_execution_time as [time]
from sys.dm_exec_query_stats as s1 cross apply sys.dm_exec_sql_text(sql_handle) as s2
where s2.objectid is null
order by last_execution_time desc
SELECT
TOP 1000 s2.dbid,
DB_NAME(s2.dbid) AS [dbname],
(
SELECT
TOP 1 SUBSTRING (
s2. TEXT,
statement_start_offset / 2 + 1,
(
(
CASE
WHEN statement_end_offset =- 1 THEN
(
len(
CONVERT (nvarchar(MAX), s2. TEXT)
) * 2
)
ELSE
statement_end_offset
END
) - statement_start_offset
) / 2 + 1
)
) AS sqll,
last_execution_time AS [time]
FROM
sys.dm_exec_query_stats AS s1 CROSS apply sys.dm_exec_sql_text (sql_handle) AS s2
WHERE
s2.objectid IS NULL
ORDER BY
last_execution_time DESC