May 30, 2003 at 6:21 pm
Is there a way to find out when an index was created for a table? I looked at the sysindex table and there's no create date field. Help please?
May 30, 2003 at 7:05 pm
I think you can query sysobjects on the name of the index to get the creation date...something like :
select [name],crdate from sysobjects where name = 'IndexName'
The link below lists out how to get details of the indexes in each table :
This query can be tweaked to get the create date from sysobjects....
my (not very good !!) tweak is as follows :
select
SO.[name] as TableName,
SI.[name] as IndexName,
SO2.crdate as IndexCreateDate,
SC.[name] as ColName,
ST.[name] as TypeVal,
CASE WHEN (SI.status & 16)<>0 THEN 'Yes' ELSE 'No' END as ClusteredIndexfrom
sysobjects SO
INNER JOIN
sysindexes SI
INNER JOIN sysindexkeys SIK
ON
SIK.[id] = SI.[id] AND
SIK.indid = SI.indid
INNER JOIN sysobjects SO2 ON
SI.[name] = SO2.[name]
INNER JOIN
syscolumns SC
INNER JOIN
systypes ST
ON
SC.xtype = ST.xtype
ON
SIK.[id] = SC.[id] AND
SIK.colid = SC.colid
ON
SO.[id] = SI.[id]
WHERE
SO.xtype = 'u' AND SI.indid > 0 AND SI.indid < 255 AND (SI.status & 64)=0
ORDER BY
TableName,IndexName,SIK.keyno
Edited by - winash on 05/30/2003 7:14:28 PM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply