how to check the index creation or modified datetime?

• deepak.a

  SSCertifiable

  Points: 5330

  More actions

  October 17, 2011 at 5:22 am

  #250092

  hi All,

  how to check the index (expect primary key and unique key) creation or modified datetime ?

  Thanks & Regrads

  Deepak.A

• Lowell

  SSC Guru

  Points: 323524

  More actions

  October 17, 2011 at 6:01 am

  #1395251

  PKs and UQ's constraints can be found in sys.objects;

  so the common fields ofr all objects like create_date and modify_date will get you what you wan:, I think:

  /*--Results

  TableNamecreate_datemodify_datename

  Tally2009-02-27 14:26:36.5772009-02-27 14:26:36.577PK_Tally_N

  Numbers2009-02-27 14:26:41.7172009-02-27 14:26:41.717PK_Numbers_Numbner

  WHORESULTS2011-09-28 16:23:16.5472011-09-28 16:23:16.547PK__WHORESULTS__54E305AD

  WHO_DATA2011-09-28 16:25:36.2002011-09-28 16:25:36.200PK__WHO_DATA__58B39691

  Empmaster2010-05-17 08:50:56.2702010-05-17 08:50:56.270PK_Empmaster

  DeptMaster2010-05-17 08:50:56.3502010-05-17 08:50:56.350PK_DeptMaster*/

  SELECT OBJECT_NAME(parent_object_id) AS TableName,

  create_date,

  modify_date,

  *

  FROM sys.objects

  WHERE type IN( 'PK', 'UQ' )

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• deepak.a

  SSCertifiable

  Points: 5330

  More actions

  October 17, 2011 at 6:07 am

  #1395255

  Hi Lowell ,

  how to check the index creation for non -PK or Unique Key , example non-clustered index creation index date or filtered index or XML index ....

  Thanks & regards

  Deepak.A

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 17, 2011 at 6:16 am

  #1395260

  Ah, how 1 little word changes everything!

  expect except!

  And no I have no idea where to get that info.

  It's not in sys.indexes, sys.stats, indexproperty nor sys.dm_db_index_physical_stats dynamic management function and the likes.

  The only useful-ish idea I have is that you should see that in your change management system!

• Lowell

  SSC Guru

  Points: 323524

  More actions

  October 17, 2011 at 6:21 am

  #1395263

  i don't believe the index_creation_date / modify date exists(or at least is exposed); i posed a similar question ofn when is a server side trace Creation_date...it's not available eaither...jsut when it was last started.

  best i could find is the last time an index was used, and that's in select * from sys.dm_db_index_usage_stats

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 17, 2011 at 6:28 am

  #1395266

  Lowell (10/17/2011)

  ---

  i don't believe the index_creation_date / modify date exists(or at least is exposed); i posed a similar question ofn when is a server side trace Creation_date...it's not available eaither...jsut when it was last started.

  best i could find is the last time an index was used, and that's in select * from sys.dm_db_index_usage_stats

  I went down that road as well.

  Nothing like 1st time used. Stats date is the same problem. Won't be valid very long.