foreachdatabase query

SELECT TOP 10
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

I was using this query to return output for queries with most reads. I would execute it on master database. But I need to use exec sp_msforeachdb statement to get outputs for each database and put a clause so that it only gets results for user databases. pls help
LVL 1
sqlserverdbaAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
this is how you do it:

create table #results(reads int, execount int, dbname sysname)

insert into #results
execute sp_msforeachdb 'use [?] SELECT TOP 10
        [Total Reads] = SUM(total_logical_reads)
        ,[Execution count] = SUM(qs.execution_count)
        ,DatabaseName = ''?''
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC'

select * from #results
0
 
sqlserverdbaAuthor Commented:
Thank you..
The query executes, there is output, but there is also a syntax error. This is the error I get
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '.'.
also I need to get only user databases in the output.
0
 
chapmandewCommented:
did you use the exact query I provided?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sqlserverdbaAuthor Commented:
yes i did
0
 
chapmandewCommented:
Hmmm...I just ran it on 3 different servers and it worked fine on all of them.  You're on 2005, right?
0
 
sqlserverdbaAuthor Commented:
yes I am. pls try parsing it on a different database no need of executing it even. In master database, it parses successfully and on a user database it says incorrect syntax.
0
 
chapmandewCommented:
works fine for me everywhere.
0
All Courses

From novice to tech pro — start learning today.