1 How to check index creation date

I want to write the query to display the index within the database and its creation date.

Here is the query:

SELECT object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc,o.create_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 create_date DESC

We can test it with below cases:

-- clean the env

IF OBJECT_ID('v1', 'v') IS NOT NULL

  DROP VIEW v1

IF OBJECT_ID('t1', 'U') IS NOT NULL

  DROP TABLE t1

IF OBJECT_ID('t2', 'U') IS NOT NULL

  DROP TABLE t2

IF OBJECT_ID('t3', 'U') IS NOT NULL

  DROP TABLE t3

IF OBJECT_ID('t4', 'U') IS NOT NULL

  DROP TABLE t4

IF OBJECT_ID('t5', 'U') IS NOT NULL

  DROP TABLE t5

-- Create the Heap table

CREATE TABLE t1 (

    c1 nchar(10) NULL,

    c2 int  primary key

    )  

-- create primary key inline in the table DDL

CREATE TABLE t2 (

    c1 nchar(10) NULL,

    c2 int  primary key

    )

-- create primary key out side of table DDL 

CREATE TABLE t3 (

    c1 nchar(10) NULL,

    c2 int NOT NULL ,

    )  

ALTER TABLE t3 

ADD CONSTRAINT PK_t3 PRIMARY KEY CLUSTERED (c2);

-- create index without constrain  

CREATE TABLE t4 (

    c1 nchar(10) NULL,

    c2 int  ,

    )  ;

Create clustered index  idx1_t4 on t4(c2);

Create nonclustered index  idx2_t4 on t4(c1);

-- create index view

create table t5 (

    c1 char(20),

    c2 int not null

)

create view dbo.v1 with SCHEMABINDING

as  select c1,c2 from dbo.t5

create unique clustered index idx_tv1 on v1(c2);

After all the tables are created, we run the query within the database. Here is the result

I also tried to run the query in the adventure work sample database. Interestingly, the table value function also consider as index and show up in the sys.indexes table.