Hello, I have the following two queries where the first one gives me values for "StatisticsDate" while the second one does not. I know it is because the object_id/index_id value is wrong but I cannot figure out what to use.
------------------------------------------------------------------------------------------
USE AcctEnc;
GO
SELECT
QUOTENAME(t.name) AS TableName
, QUOTENAME(i.name) AS IndexName
, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
, ROUND(ISNULL(ps.avg_fragmentation_in_percent,0), 2) AS Fragmentation
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'LIMITED') ps ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
WHERE t.[type] = 'U'
AND t.is_ms_shipped = 0
ORDER BY StatisticsDate
, IndexName;
GO
------------------------------------------------------------------------------------------
USE master;
GO
SELECT
QUOTENAME(t.name) AS TableName
, QUOTENAME(i.name) AS IndexName
, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
, ROUND(ISNULL(ps.avg_fragmentation_in_percent,0), 2) AS Fragmentation
FROM AcctEnc.sys.tables t
INNER JOIN AcctEnc.sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_physical_stats(db_id('AcctEnc'), NULL, NULL, NULL, 'LIMITED') ps ON i.[object_id] = ps.[object_id]
AND i.index_id = ps.index_id
WHERE t.[type] = 'U'
AND t.is_ms_shipped = 0
ORDER BY StatisticsDate
, IndexName;
GO
Thank you for your time.
DJJ