Find Index creation date
-
Hi All,
I want find the date time on which an index was created. How will you do this?
I have looked in to all the system tables and I am not able to find this information. Sysobjects 'crdate' column lists the creation date of objects like
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Please help!
Thanks
-
Unless the index is unique (UQ) or a primary key (PK). There's no way to get this info out of sql server (2000).
-
I suspect that part of the reason why it's NOT available is the ambiguity of definition. Is it the date that the index was first established, or the latest date of reordering that you are seeking? Or some other definition of 'creation date'?
You see the challenge? Here we are, all speaking/typing in English, but not understanding the meaning.
-
Hi Steve,
Sorry for not being specific....I want the Date on which the index was first created. Is there a way to do it in SQL Server 2005???
Thanks!
-
IF you can guarantee that records are NEVER removed from the table you are indexing on, and IF you can guarantee that the index was built with the table originally, and IF there is a date field captured in the data, then you could 'DEFINE' the index creation date as the date the first record was inserted.
But that's not really true, either. Because you have to use DDL to create the table, with it's index, before you can insert data.
So, from where I sit, now that I've had a weekend to mull the question over, it seems like the question is really a substitute for some other question....
Example: Table 1, with one index, in SQL Server 2005, was converted over from SQL 2000 database, which was converted over from SQL 7 database... which date is the date you want? when the DB converted to 2005, or was created in SQL 7?
We need more details.... and even then can't guarantee a meaningful answer.
-
Here's the deal. SQL Server Doesn't keep the information you really want, so there are some tricks to get something that is close. Is there a specific reason you need this information? That might help us to get the answer you need.
As noted earlier. If you what you were looking for was the date the clustered index was created. the sys.objects view will work (almost, it is not guarenteed to be right). Just get the info on the table (type U) You could also use the PK value, if has a primary key.
-
Thanks for all your response!!!
Well, the actual requirement is this - We were doing performance tuning of a SQL Server Schema. Then we began analyzing the objects and also documenting stuff as we proceeded.....
On the process, I just wanted to document our findings and create some impressive reports, so I was looking around for the creation date of all the Clustered and NonClustered Indexes when it was first created on a Table - No matter when the table was created...., but I found it
wierd that this information was not available in SQL Server.I have also exhausted all options suggested by my peers and googled around endlessly... but in vain..
(I would also like to know if there is any option in SQL Server 2005. )
PS: Sysobjects doesnot contain information about the Indexes, I have already mentioned it in my first post.
-
Try this:
select name,stats_date(object_id,index_id) as Create_date
From sys.indexes
-
paul wilson-360699 (7/27/2010)
select name,stats_date(object_id,index_id) as Create_dateFrom sys.indexes
That's the date the statistics were last updated. It'll be any one of:
Date index was created
Date index was rebuilt
Date the auto_update of the statistics last happened
Date the statistics were last manually updated.
Unless the index is really, really, really new, it'll be one of the last 3, not the creation date of the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
yes there is one way to find out....
correct me if i m wrong...
if index is created on a filegroup and that filegroup is associated with a data file.
then creation date of data file=creation date of index
-
azadsale (8/5/2010)
if index is created on a filegroup and that filegroup is associated with a data file.then creation date of data file=creation date of index
Incorrect, in two ways.
Files are associated with filegroups - there can be more than once file in a filegroup and anything in a filegroup is striped across all the files.
The creation date of the filegroup is the date that the ALTER DATABASE was run to add the filegroup. The creation date of the file is the date that the ALTER DATABASE statement was run to add the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
thanks gila.....i realised after some-time that its not right.but didnt exactly figure out why it cant be right.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply