Who Created Indexes

• RCC-JAXDBA

  Mr or Mrs. 500

  Points: 557

  More actions

  February 14, 2008 at 8:36 am

  #186602

  We have a group of DBA's all acrosds the US and they all have access to the PROD servers. We had a huge table (88 mill rows) mysteriously get 10 indexes added to it and noone will own up to doing it. Is there a SQL statement of dynamic view to see who created indexes on a server

• Tommy Bollhofer

  SSChampion

  Points: 14940

  More actions

  February 14, 2008 at 8:54 am

  #778409

  From the management studio, right-click the database and/or server in question and select the reports option, and the Schema Changes History report. Here is the T-SQL if you would like to filter 😀

  BEGIN TRY

  DECLARE @enable int

  SELECT TOP 1 @enable = CONVERT(int,value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE name = 'default trace enabled'

  IF @enable = 1 --default trace is enabled

  BEGIN

  DECLARE @d1 datetime;

  DECLARE @diff int;

  DECLARE @curr_tracefilename varchar(500);

  DECLARE @base_tracefilename varchar(500);

  DECLARE @indx int ;

  DECLARE @temp_trace table (

  obj_name nvarchar(256) collate database_default

  , database_name nvarchar(256) collate database_default

  , start_time datetime

  , event_class int

  , event_subclass int

  , object_type int

  , server_name nvarchar(256) collate database_default

  , login_name nvarchar(256) collate database_default

  , application_name nvarchar(256) collate database_default

  , ddl_operation nvarchar(40) collate database_default

  );

  SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1 ;

  SET @curr_tracefilename = REVERSE(@curr_tracefilename)

  SELECT @indx = PATINDEX('%\%', @curr_tracefilename)

  SET @curr_tracefilename = REVERSE(@curr_tracefilename)

  SET @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

  INSERT INTO @temp_trace

  SELECT ObjectName

  , DatabaseName

  , StartTime

  , EventClass

  , EventSubClass

  , ObjectType

  , ServerName

  , LoginName

  , ApplicationName

  , 'temp'

  FROM ::fn_trace_gettable( @base_tracefilename, default )

  WHERE EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

  -- AND ObjectName = 'SOMESP'

  -- AND LoginName <> 'SOMEDOMAIN\SOMEUSER'

  -- AND LoginName <> 'SOMESQLUSER'

  UPDATE @temp_trace SET ddl_operation = 'CREATE' WHERE event_class = 46

  UPDATE @temp_trace SET ddl_operation = 'DROP' WHERE event_class = 47

  UPDATE @temp_trace SET ddl_operation = 'ALTER' WHERE event_class = 164

  SELECT @d1 = MIN(start_time) FROM @temp_trace

  SET @diff= DATEDIFF(hh,@d1,GetDate())

  SET @diff=@diff/24;

  SELECT @diff AS difference

  , @d1 AS date

  , object_type AS obj_type_desc

  , *

  FROM @temp_trace WHERE object_type NOT IN (21587)

  ORDER BY start_time desc

  END

  ELSE

  BEGIN

  SELECT TOP 0 1 AS difference, 1 AS date, 1 AS obj_type_desc, 1 AS obj_name, 1 AS dadabase_name, 1 AS start_time, 1 AS event_class, 1 AS event_subclass, 1 AS object_type, 1 AS server_name, 1 AS login_name, 1 AS application_name, 1 AS ddl_operation

  END

  END TRY

  BEGIN CATCH

  SELECT -100 AS difference

  , ERROR_NUMBER() AS date

  , ERROR_SEVERITY() AS obj_type_desc

  , ERROR_STATE() AS obj_name

  , ERROR_MESSAGE() AS database_name

  , 1 AS START_TIME, 1 AS EVENT_CLASS, 1 AS EVENT_SUBCLASS, 1 AS OBJECT_TYPE, 1 AS SERVER_NAME, 1 AS LOGIN_NAME, 1 AS APPLICATION_NAME, 1 AS DDL_OPERATION

  END CATCH

  Tommy

  Follow @sqlscribe