October 17, 2011 at 5:22 am
hi All,
how to check the index (expect primary key and unique key) creation or modified datetime ?
Thanks & Regrads
Deepak.A
October 17, 2011 at 6:01 am
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
October 17, 2011 at 6:07 am
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
October 17, 2011 at 6:16 am
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!
October 17, 2011 at 6:21 am
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
October 17, 2011 at 6:28 am
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.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy