Get index creation date from SQL server

Get index creation date from SQL server

How can i find the create date of index. I am using SQL2008 R2. I checked sys.indexes but it does not have create date so i joined the query with sys.objects but the thing is that, the object id for index and the table containing that index is same.... I am using this queryselect i.name,i.object_id,o.create_date,o.object_id,o.name from sys.indexes i join sys.objects o on i.object_id=o.object_id where i.name='Index_Name'

Thanks

Answer by marc_s for Get index creation date from SQL server

Try this:

SELECT       i.name 'Index Name',      o.create_date  FROM       sys.indexes i  INNER JOIN       sys.objects o ON i.name = o.name  WHERE       o.is_ms_shipped = 0      AND o.type IN ('PK', 'FK', 'UQ')  

The object_id refers to the table the index is created on....

Answer by gbn for Get index creation date from SQL server

For indexes that are constraints, then see marc_s' answer

For other indexes, you'd have to use STATS_DATE to get the creation time of the assoicated index (every index has statistics on it)

Something like (not tested)

SELECT STATS_DATE(OBJECT_ID('MyTable'),   (SELECT index_id FROM sys.indexes WHERE name = 'Index_Name'))  

This relies on the sys.indexes to sys.stats links

Edit: there is no way to find out as far as anyone can find out. Sorry.

Answer by R? NATH for Get index creation date from SQL server

select       crdate, i.name, object_name(o.id)  from       sysindexes i  join       sysobjects o ON o.id = i.id   where       i.name = 'My_Index_Name'  

Answer by Alex for Get index creation date from SQL server

When PK or UK is created, SQL Server automatically creates unique index for that constraints. The create_date of those constraints will be the same as the create date for the corresponding indexes.

Since the sys.indexes view does not have create_date column it is absolutely useless for searching this kind of information. Furthermore, object_id column in this view will never refer to the corresponding constraint. It will point to the table the index belongs to. The following test will demonstrate the point:

CREATE TABLE dbo.TEST_T1  (      COLUMN_1 INT NOT NULL,       COLUMN_2 INT NOT NULL,      CONSTRAINT PK_TEST_T1 PRIMARY KEY (COLUMN_1)  )  GO  WAITFOR DELAY '00:00:01';    ALTER TABLE dbo.TEST_T1      ADD CONSTRAINT UK_TEST_T1 UNIQUE (COLUMN_2)  GO    SELECT O.name, O.object_id, O.create_date, I.object_id, I.name AS index_name  FROM sys.objects AS O       LEFT OUTER JOIN sys.indexes AS I ON O.object_id = i.object_id  WHERE O.name IN ('TEST_T1', 'PK_TEST_T1', 'UK_TEST_T1')  

The result is:

name        object_id   create_date             object_id   index_name  PK_TEST_T1  272720024   2015-03-17 11:02:47.197 NULL        NULL  TEST_T1     256719967   2015-03-17 11:02:47.190 256719967   PK_TEST_T1  TEST_T1     256719967   2015-03-17 11:02:47.190 256719967   UK_TEST_T1  UK_TEST_T1  288720081   2015-03-17 11:02:48.207 NULL        NULL  

So, if you want to see create_date for PK or UK indexes there is no need to join with sys.indexes. You should select from sys.objects:

SELECT name, object_id, create_date  FROM sys.objects  WHERE name IN ('PK_TEST_T1', 'UK_TEST_T1')  AND type IN ('PK', 'UQ')  

The result is:

name        object_id   create_date  PK_TEST_T1  272720024   2015-03-17 11:02:47.197  UK_TEST_T1  288720081   2015-03-17 11:02:48.207  

Answer by WaitForPete for Get index creation date from SQL server

Simple query to list indexes in descending date (of statistics) order. This date is the sate of last statistics update, so is only reliable for recently created indexes.

select STATS_DATE(so.object_id, index_id) StatsDate  , si.name IndexName  , schema_name(so.schema_id) + N'.' + so.Name TableName  , so.object_id, si.index_id  from sys.indexes si  inner join sys.tables so on so.object_id = si.object_id  order by 1 desc  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72