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 13:55:54 -0500

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: Index Creation Date
    ... Mistake, mistake.. ... >>Join sysindexes and sysobjects and use the crdate in the ... Read details on SQL Server Books Online. ...
    (microsoft.public.sqlserver.security)
  • DBCC
    ... DBCC results for 'sysobjects'. ... DBCC results for 'sysindexes'. ... There are 5613 rows in 91 pages for object 'syscolumns'. ...
    (microsoft.public.sqlserver.server)
  • dynamic sp build
    ... I currently build the script in a temp table that hold the statements for the new stored procedure. ... CREATE PROCEDURE dbo.x_Build_GetByPK @intablename varcharAS ... values ('FROM sysobjects '); ...
    (microsoft.public.sqlserver.programming)
  • Re: Replicating new table
    ... such a method would involve constantly polling the sysobjects and sysindexes ... "Jim" wrote in message ...
    (microsoft.public.sqlserver.replication)
  • Re: Join help
    ... particular, I wanted to relate sysusers, sysobjects and syscolumns so ... PS. you'll have to guess what the aliases stand for. ...
    (comp.databases.sybase)