AFAIK, there is no system object gives you the information of Index creation Date in SQL Server. If the Clustered index is on PK then the creation date can get from sysobjects or sys.objects. But that is not the case always.
This query which uses STATS_DATE() function to get the STATISTICS updated date. This will not give you accurate result if you are updating STATISTICS explicitly. The logic behind the query is, if you rebuild indexes the STATISTICS are also being updated at the same time. So if you are not explicitly updating STATISTICS using UPDATE STATISTICS tableName command , then this query will give you the correct information
--In SQL Server 2000
Select Name as IndexName,
STATS_DATE ( id , indid ) as IndexCreatedDate
From sysindexes where id=object_id('HumanResources.Employee')
-- In SQL Server 2005
Select Name as IndexName,
STATS_DATE ( object_id , index_id ) as IndexCreatedDate
From sys.indexes where object_id=object_id('HumanResources.Employee')
Wednesday, June 18, 2008
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
Thanks, this is exactly what I needed.
This is Index statistics creation date not the Index creation date :-).Is there a way to get index creation Date.
Sagesh,
If you log your DDL statements with a trigger then you will be able to reference your log table and get a *very close approximation*... I say that b/c the trigger will fire when the DDL statement (create_object schema.name) is issued, and the log will record the time-stamp for when this is occurs. This will be within fractions of a second of the actual date/time of creation - which should be close enough. Code below
Create the structure for a log table:
CREATE TABLE [dbo].[log_local_database_events](
[log_DB_ID] [int] IDENTITY(1,1) NOT NULL,
[log_DB_Post] [datetime] NOT NULL DEFAULT (getdate()),
[log_DB_DatabaseID] [int] NOT NULL DEFAULT (db_id()),
[log_DB_ObjectId] [int] NULL,
[log_DB_ObjectName] [nvarchar](50) NOT NULL,
[log_DB_User] [nvarchar](50) NOT NULL DEFAULT (suser_name()),
[log_EventType] [nvarchar](50) NOT NULL,
[log_TSQL] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[log_DB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is the code for a Database level DDL Trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @Data XML;
DECLARE @Schema sysname;
DECLARE @Object sysname;
DECLARE @ObjectID sysname;
DECLARE @EventType sysname;
SET @Data = EVENTDATA();
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @Schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @Object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @Object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @Object IS NOT NULL
SET @ObjectID = Object_ID(''+@Object+'')
IF @EventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
INSERT [dbo].[log_local_database_events]
(
[log_DB_Post],
[log_DB_DatabaseId],
[log_DB_ObjectId],
[log_DB_ObjectName],
[log_DB_User],
[log_EventType],
[log_TSQL]
)
VALUES
(
GETDATE(),
DB_ID(),
@ObjectID,
CONVERT(sysname, @object),
CONVERT(sysname, CURRENT_USER),
@eventType,
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
);
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
Post a Comment