Re: Index Creation Date

From: Anith Sen (anith@bizdatasolutions.com)
Date: 06/27/02 

From: "Anith Sen" <anith@bizdatasolutions.com>
Date: Thu, 27 Jun 2002 14:41:40 -0500

Mistake, mistake.. I guess I was looking at something like

select sysindexes.name, sysobjects.crdate
from sysindexes, sysobjects
where sysindexes.id = sysobjects.id
and sysindexes.indid between 1 and 254

and crdate will display only the table creation date. As you pointed out
I think you cannot get the index creation time from the system tables.

In SQL 200 you can use DBCC SHOW_STATISTICS to find out when the index
statistics were last updated

With SQL 7, you can use the STATS_DATE function to determine when the index
statistics were last updated. However, this date will be more recent if
stats were updated after the create or rebuild.

- Anith

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:r8pmhusarg4339819i55252kni1e175vmh@4ax.com...
> 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

  • Is there a way to indentify when a index was created?
    ... sysobjects but nothing like that in sysindexes. ... SQL Server 2000 EE ... sp3. ...
    (microsoft.public.sqlserver.programming)
  • objects
    ... How to find the invalid and corrupted objects in ... sql server 2000? ... I tried sysindexes and sysobjects tables. ...
    (microsoft.public.sqlserver.server)
  • Re: Table Creation DateTime
    ... If you are using proper permissions in place then from the sysobjects table ... In my production system I can see a user table Create Date got ... > does any of the SQL server operation does change the Create Date of the ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.server)
  • Re: OBJECT Level Permissions
    ... Pro SQL Server 2000 Database Design ... > There isn't a generic collection object that would allow you to GRANT EXEC ... > on all procs in a single statement. ... > However you could build a cursor that looks at sysobjects and loops though ...
    (microsoft.public.sqlserver.programming)
  • Re: Orphan Indexes?
    ... Consider yourself lucky that you didn't work out how to delete the rows from sysindexes. ... "If it bleeds, we can fix it". ... is a bug in SQL Server, MS can probably rather quickly find a fix if you open a case... ... I have tried changing the owner of the table and then performing ...
    (microsoft.public.sqlserver.programming)