Index - Sql Server 2005

• kausmail

  SSC Enthusiast

  Points: 181

  More actions

  July 14, 2011 at 8:52 am

  #244303

  How to find the user that added the index on a specific table?

  Thanks

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 14, 2011 at 9:17 am

  #1352895

  It might be in the default trace. Other than that, no way.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• upstart

  Ten Centuries

  Points: 1255

  More actions

  July 14, 2011 at 2:57 pm

  #1353118

  I don't think there is a way to find out WHO created it, but you can see WHEN it was created:

  select

  crdate,

  i.name,

  object_name(o.id)

  from

  sysindexes i

  join sysobjects o ON o.id = i.id

  order by

  crdate desc

  Other then that, in the future, you can create a DDL trigger to monitor and log anytime an Index is created or modified.

  here is a link that provides a little more information: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/31/capturing-index-operations-using-a-ddl-trigger.aspx