从 SQL Server 获取索引创建日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7579932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get index creation date from SQL server
提问by vinay
How can I find the create date of an index. I am using SQL2008 R2.
I checked sys.indexesbut 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.
如何找到索引的创建日期。我正在使用SQL2008 R2. 我检查过，sys.indexes但它没有创建日期，所以我用sys.objects. 问题是索引的对象 id 和包含该索引的表是相同的。
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!
谢谢！
回答by gbn
For indexes that are constraints, then see marc_s' answer
对于约束索引，请参阅 marc_s 的答案
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)
对于其他索引，您必须使用 STATS_DATE 来获取关联索引的创建时间（每个索引都有统计信息）
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.statslinks
这依赖于 sys.indexes 到sys.stats 的链接
Edit: there is no way to find out as far as anyone can find out. Sorry.
编辑：任何人都无法找到。对不起。
回答by WaitForPete
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
回答by Dave Hodgson
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:
这现在是一个很长的死线程，但是来自 SQLPanda 的以下查询为我提供了我在 Azure SQL 上需要的非聚集索引的信息：
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
归功于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.
我刚刚添加了修改日期，因为那是我感兴趣的信息。
回答by marc_s
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_idrefers to the table the index is created on....
该object_id指指数在创建表....
回答by ?Rü? NATH
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'
回答by Alex
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.
创建 PK 或 UK 时，SQL Server 会自动为该约束创建唯一索引。这些约束的 create_date 将与相应索引的创建日期相同。
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:
由于 sys.indexes 视图没有 create_date 列，因此搜索此类信息绝对无用。此外，此视图中的 object_id 列永远不会引用相应的约束。它将指向索引所属的表。下面的测试将证明这一点：
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:
因此，如果您想查看 PK 或 UK 索引的 create_date，则无需加入 sys.indexes。您应该从 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
回答by Parassharma1990
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.
但是索引的主要问题是，当我们重建或重组索引时，索引创建日期会更改为上次重建或重组索引的日期。
