sqlserverdba
asked on
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.sq
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
[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.sq
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
did you use the exact query I provided?
ASKER
yes i did
Hmmm...I just ran it on 3 different servers and it worked fine on all of them. You're on 2005, right?
ASKER
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.
works fine for me everywhere.
ASKER
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.