CODEWITHSUNDEEP
sqlsql-server-2008
vinay
vinay

Reputation: 1034

Get index creation date from SQL server

How can I find the create date of an index. I am using SQL2008 R2. I checked sys.indexes but it does not have a create date so I joined the query with sys.objects. The thing is that the object id for an index and the table containing that index is same.

I am using this query...

select 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!

Upvotes: 41

Views: 140085

Answers (7)

Parassharma1990
Parassharma1990

Reputation: 21

USE [YourDB Name]
SET NOCOUNT ON

DECLARE @Table_Name varchar(200)
DECLARE @Index_Name varchar(200)
DECLARE @Index_Type varchar(50)

DECLARE Indx_Cursor CURSOR
STATIC FOR
            select s_tab.name as Table_Name,
                   s_indx.name as Index_Name,
                   s_indx.type_desc as Index_Type
            from sys.indexes s_indx
            inner join sys.tables s_tab 
                    on s_tab.object_id=s_indx.object_id
            where s_indx.name is not null;
            
OPEN Indx_Cursor
    IF @@CURSOR_ROWS > 0
        BEGIN 
            FETCH NEXT FROM Indx_Cursor INTO @Table_Name,@Index_Name,@Index_Type

WHILE @@Fetch_status = 0
  BEGIN
    INSERT INTO INDEX_HISTORY(table_name,index_name,Index_Type,Created_date) 
    SELECT  @Table_Name,@Index_Name,@Index_Type,
            STATS_DATE(OBJECT_ID(@Table_Name), 
            (SELECT index_id FROM sys.indexes 
              WHERE name = @Index_Name))as Index_create_Date
                    
                    FETCH NEXT 
                          FROM Indx_Cursor 
                          INTO @Table_Name,@Index_Name,@Index_Type
                END
        END

CLOSE Indx_Cursor
DEALLOCATE Indx_Cursor

select distinct * from index_history

But the main problem with indexes is that when we rebuild or reorganize indexes then the index creation date gets changed to the date when the index was last rebuilt or reorganized.

Upvotes: 1

gbn
gbn

Reputation: 432200

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 associated 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.

Upvotes: 17

Dave Hodgson
Dave Hodgson

Reputation: 107

This is now quite a long dead thread but the below query from SQLPanda got me the info I needed on Azure SQL for a non clustered index:

SELECT OBJECT_NAME(i.object_id) AS TableName, i.object_id, i.name, i.type_desc,o.create_date, o.modify_date,o.type,i.is_disabled
FROM   sys.indexes i
        INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.type NOT IN ('S', 'IT')
  AND o.is_ms_shipped = 0 
  AND i.name IS NOT NULL
ORDER BY modify_date DESC

Credit to http://www.sqlpanda.com/2013/10/how-to-check-index-creation-date.html

I just added the modified date since that was the info I was interested in.

Upvotes: 5

ÄRÜÑ NATH
ÄRÜÑ NATH

Reputation: 23

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'

Upvotes: -1

WaitForPete
WaitForPete

Reputation: 467

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

Upvotes: 16

Alex
Alex

Reputation: 423

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

Upvotes: 3

marc_s
marc_s

Reputation: 754268

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....

Upvotes: 4

Related Questions