May 8, 2018 at 2:06 pm
This is a fun one. I need to run the same query against every db on the database and aggregate the results. Here's the fun:
1. One single query. No cursors or sp_MSforeachdb.
2. No UNIONs because it has to be dynamic enough to run on any server and each server will have a different set of databases.
I'm working on something for splunk if you know anything about that.
The query is looking at system tables, so the query is fine to be the same against any db of the same SQL version.
SELECT DB_NAME(qt.dbid),
SCHEMA_NAME(o.schema_id) AS schema_name,
o.name AS object_name,
SUM(qs.total_worker_time) as total_worker_time
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE o.type = 'FN'
GROUP BY DB_NAME(qt.dbid),
SCHEMA_NAME(o.schema_id),
o.name
ORDER BY SCHEMA_NAME(o.schema_id), DB_NAME(qt.dbid), o.name
That's a simplified version of what I'm doing. I need to run that against every database on the server and and have it all return in one nice neat result set ordered by dbname, schemaname, objname. Again, I can't do it in a union because the database names and even existence is fluid.
May 8, 2018 at 2:43 pm
Unless you pre-build a combined table of all the sys.objects 'FN' entries for each db, you won't be able to do this. AFAIK, you can't get the object type and schema name (you can get the object name) without using a system table in the db where the object exists.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 8, 2018 at 4:27 pm
As Scott mentioned, this isn't going to work.
What I'd say is build a view that aggregates things. Dbs change? Use a proc that can re-read the list and rebuild the view using dynamic SQL. You could even use a DDL trigger on create/drop database to run this.
May 9, 2018 at 5:29 am
Thanks. I was thinking it wasn't possible, but wanted to make sure I wasn't overreacting and missing something.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply