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.
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.
ReplyDeleteExactly. Not really the index date
DeleteExcellent blog, I welcome your interest about to post blogs. It will help many of them to update their skills in their interesting field.
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | PLC Training in Kerala | Embedded Training in Chennai | AutoCAD Training in Chennai | VLSI Training in Chennai | Inplant Training in Chennai
Looks like showing table creation date
ReplyDelete