February 29, 2008 at 9:12 am
Ok, I'm not sure what I'm doing wrong here, but pretty sure my 2005 statement is fine, my 2000 is the issue. It has to be on my join ... I'm pulling back to much data ...
I know it's gonna be glaringly obvious, but I'm not seeing it ...
2000
SELECT
o.nameAS [Table Name]
,i.nameAS [Index Name]
,CASE indid
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'CLUSTERED'
ELSE'NON CLUSTERED'
ENDAS [Index Type]
,STATS_DATE(i.id, i.indid)AS [Last Stats Update]
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE o.type = 'U'
AND i.indid > 0
ORDER BY 1,2,4
2005
SELECT
t.nameAS [Table Name]
,i.nameAS [Index Name]
,i.type_descAS [Index Type]
,STATS_DATE(i.OBJECT_ID,i.index_id) AS [Last Stats Update]
FROM
sys.indexes i JOIN
sys.tables t ON t.OBJECT_ID = i.OBJECT_ID
WHERE i.type > 0
ORDER BY 1,2,4
February 29, 2008 at 12:26 pm
Well, sys.indexes does not pull back all the objects that sysindexes does ... so perhaps I'm doing it wrong in 2k5.
Any advice would be most welcome.
Thanks
February 29, 2008 at 12:36 pm
Hi Adam,
In 2000, you are also seeing the Statistics of the tables as well. Atleast that is what I am seeing here. They all start with _WA. I modified your query a bit and got the right number of rows.
SELECT
o.nameAS [Table Name]
,i.nameAS [Index Name]
,CASE indid
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'CLUSTERED'
ELSE'NON CLUSTERED'
ENDAS [Index Type]
,STATS_DATE(i.id, i.indid)AS [Last Stats Update]
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE o.type = 'U'
AND i.indid > 0
and i.name not like '_WA%'
ORDER BY 1,2,4
-Roy
February 29, 2008 at 12:39 pm
Yeah I saw that ... what are those _WA tables?
February 29, 2008 at 12:52 pm
If I am not mistaken, they are the Statistics details of each Object.These are Statistics generated by the system itself.
-Roy
February 29, 2008 at 12:54 pm
Ah ok, well, thanks for the help and info!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply