djadkison
asked on
Index Creation date
Can someone show me how to write a query or run a stored procedure that will let me know when an index has been created?
I can't comment on 2K, but for SQL7 I don't think that you can get this information. SQL Server does not provide an audit trail for changes you make to structual parts of the database. I don't think that this has changed for SQL 2K. Check Books Online under the sysindexes entry.
Try this query:
SELECT u.name + '.' + o.name tablename,
i.name index_name,
o.crdate index_creation_date
FROM sysusers u JOIN
sysobjects o ON u.uid=o.uid JOIN
sysindexes i ON o.id=i.id
WHERE o.type='U'
AND i.status & 64=0
AND i.indid < 255
ORDER BY tablename, index_creation_date
SELECT u.name + '.' + o.name tablename,
i.name index_name,
o.crdate index_creation_date
FROM sysusers u JOIN
sysobjects o ON u.uid=o.uid JOIN
sysindexes i ON o.id=i.id
WHERE o.type='U'
AND i.status & 64=0
AND i.indid < 255
ORDER BY tablename, index_creation_date
ASKER
The Creation date appears to be the date of creation for the table, not the index. I appreciate the information. It is not apparently available.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's okay. At least you tried to help. And for that matter, the code gave my DBA some other ideas.
Thanks again, Dave
Thanks again, Dave