Re: Index Creation Date

From: Sue Hoegemeier (Sue_H@nomail.please)
Date: 06/27/02 

From: Sue Hoegemeier <Sue_H@nomail.please>
Date: Thu, 27 Jun 2002 13:21:29 -0600

Anith,
When I run this on SQL Server 2000, SP2, the crdate for
indexes is the create date for the table not for the index.

-Sue

On Thu, 27 Jun 2002 13:55:54 -0500, "Anith Sen"
<anith@bizdatasolutions.com> wrote:

>Join sysindexes and sysobjects and use the crdate in the
>sysobjects table. Read details on SQL Server Books Online.
>
>SELECT
> u.name + '.' + o.name tablename,
> i.indid,
> i.name index_name,
> CASE WHEN (i.status & 2)<>0 THEN 'yes' ELSE 'no' END unq,
> CASE WHEN (i.status & 2048)<>0 THEN 'yes' ELSE 'no' END prm,
> CASE WHEN (i.status & 16)<>0 THEN 'yes' ELSE 'no' END clu,
> o.crdate
>FROM
> sysusers u JOIN
> sysobjects o ON u.uid=o.uid JOIN
> sysindexes i ON o.id=i.id
>WHERE
> o.type='U' AND
> i.status & 64=0 AND
> i.indid < 255
>ORDER BY
> tablename,
> prm DESC,
> i.indid
>
>- Anith
>
>
>"Puneet Parashar" <parashar22@hotmail.com> wrote in message
>news:epUludgHCHA.2436@tkmsftngp10...
>> Hi,
>>
>> My developers are creating many indexes by themseleves, and i want to
>> know when was the index created. i looked into sysindexes table but that
>> does not have any information about the creation date of the index. If
>> any one can help me to determine the date on which indexes were
>> created/recreated that would be a great help.
>>
>> Thanks,
>> Puneet
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>> Don't just participate in USENET...get rewarded for it!
>

Relevant Pages

  • Re: delete tables
    ... SELECT crdate ... FROM sysobjects ... SQL Server MVP ... "Lud" wrote in message ...
    (microsoft.public.sqlserver.server)
  • Re: create list of string matches for "LIKE" criteria
    ... Thanks Anith. ... I guess I felt that as SQL Server allow an alias or an expression, ... > aliases, ...
    (microsoft.public.sqlserver.programming)
  • Re: query for identity columns
    ... Need SQL Server Examples check out my website at ... "Hassan" wrote in message ... > Neat Anith.. ... > How can i take it to the next step and run the same for every database. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can we rely on object_id always increasing?
    ... is it true that the object_id of the view if I query sysobjects ... select name, crdate, id from sysobjects where type = 'U' order by crdate ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Clear SQL Server Cache?
    ... Thanks Anith, I checked these out. ... memory so I'm thinking it had to be storing other things as well. ... > Look up DBCC FREEPROCCACHE & DBCC DROPCLEANBUFFERS in SQL Server Books ...
    (microsoft.public.sqlserver.programming)