CODEWITHSUNDEEP
sql-server-2014
Clint
Clint

Reputation: 89

AGGREGATE Function Group By

I am trying to count my total number of tables, total number of rows, the last time the DB has been updated and last time a stored proc was executed. I have a total of 4 tables and 153 rows after manually counting. When I add the sEPS.last_execution_time to the end of my SELECT, it throws off the numbers. Is there a way I can successfully AGGR everything, then pull back the last execution date? 

SELECT
  COUNT(SCHEMA_NAME(sO.schema_id)) AS TableCount,
  SUM(sPTN.Rows) AS [RowCount],
  MAX(sO.modify_date) AS LastUpdated,
  (sEPS.last_execution_time) AS 'LastExecuted'
FROM sys.objects AS sO
INNER JOIN sys.partitions AS sPTN ON sO.object_id = sPTN.object_id
INNER JOIN sys.dm_exec_procedure_stats AS sEPS ON sO.object_id = sEPS.object_id
WHERE sO.type = 'U'
GROUP BY sEPS.last_execution_time

When I run the above code, I'm getting 5 rows back(there should only be one) and I am get one table 3 times. Any help is appreciated. THANKS

Upvotes: 0

Views: 64

Answers (1)

Lamak
Lamak

Reputation: 70648

The last time an sp was executed can't be joined to the rest of the tables, because the other are joined by the object_id of the table. You could do something like this:

SELECT  COUNT(DISTINCT SO.object_id) AS TableCount,
        SUM(sPTN.Rows) AS [RowCount],
        MAX(sO.modify_date) AS LastUpdated,
        MAX(LastExecuted) LastExecuted
FROM sys.objects AS sO
INNER JOIN sys.partitions AS sPTN 
    ON sO.object_id = sPTN.object_id
CROSS JOIN (SELECT MAX(last_execution_time) LastExecuted
            FROM sys.dm_exec_procedure_stats) AS sEPS 
WHERE sO.type = 'U';

Upvotes: 1

Related Questions