Tuesday, October 22, 2013

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

image

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.

image

1 comments:

  1. How do you figure this gets the index create date? You are obtaining the date from sys.objects, i.e. the actual table to which the index is associated.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates