none
Getting DB Name in sys.dm_exec_query_stats Query RRS feed

  • Question

  • How can I get the database name in the following query? Thanks.

    SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
        ((CASE statement_end_offset 
            WHEN -1 THEN DATALENGTH(ST.text)
            ELSE QS.statement_end_offset END 
                - QS.statement_start_offset)/2) + 1) AS statement_text,
    	 DB_NAME(dbid) AS db_name, 
    	 last_execution_time AS last_execution,
    	 QS.*, ST.*
         FROM sys.dm_exec_query_stats AS QS
         CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
    	 WHERE ST.text like '%vSalesPersonSalesByFiscalYears%'
    	 -- WHERE DB_NAME(dbid) = 'AdventureWorks2012'
    	 ORDER BY last_execution DESC;
    	/*
    	statement_text	db_name	last_execution
    SELECT TOP 1000 [SalesPersonID]
          ,[FullName]
          ,[JobTitle]
          ,[SalesTerritory]
          ,[2006]
          ,[2007]
          ,[2008]
      FROM [AdventureWorks2012].[Sales].[vSalesPersonSalesByFiscalYears]	NULL	2013-04-02 01:51:03.247
      */


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, April 2, 2013 5:55 AM

Answers