0

I wrote a query, based on an example I found in this StackOverflow thread, to display the date of the most recent statistics update on the columns of a particular table. Yesterday it was working just fine on my local test system. Today, it returns no rows, unless I comment out the where clause. Then it returns just under 400 rows, but none of them are for the desired table.

I don’t know what might have changed in the interval, and I don’t see how there can fail to be some date for the statistics on that table.

The script is shown below

Any advice would be helpful

SELECT 
       sch.name as [schema],
       o.name as "Table",
       c.name as "Col Name",
       [s].[name] as "Stats",
       [sp].[last_updated] as "Last Updated",
       [sp].[rows],
       [sp].[rows_sampled],
       [sp].[modification_counter] as "Mod Cnt"
FROM
       [sys].[stats] as [s] inner join sys.stats_columns as [sc]
             on (s.stats_id = sc.stats_id) and (s.object_id= sc.object_id)
       inner join sys.columns as [c]
             on c.object_id=sc.object_id and c.column_id=sc.column_id
       inner join sys.objects as o
             on s.object_id=o.object_id
       inner join sys.schemas as sch
             on o.schema_id=sch.schema_id
       OUTER APPLY sys.dm_db_stats_properties ([s].[object_id], [s].[stats_id]) as [sp]
       where [o].[name] = 'ssi_financialdetail'
       ORDER by [sp].[last_updated] ASC
David Siegel
  • 221
  • 2
  • 19
  • 3
    The scope of your query is to your database, so make sure you are running it against the user database not master. (i.e. check the drop down in the top left of SSMS, or add `USE [YourDatabase] GO` at the top of the script. – S3S Jan 29 '19 at 19:14
  • @scsimon Thank you. Yes, I just added "Use " to the start of the script. That did it. if you repost this as a formal answer, I will accept it. – David Siegel Jan 29 '19 at 19:16

0 Answers0