The ordinal position for a column changed without my knowledge

Question

I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way.

I've been researching this for quite some time and am coming up empty. I also could not reproduce it in testing.

I have informed the developers that they are not allowed to run queries that do not explicitly name columns, for exactly this reason.

In the mean time, though, it's driving me crazy that I have no idea how we came to this state of affairs.

score 6 · Accepted Answer · edited Apr 13 '17 at 12:42

Someone changed the table, simple as that. Maybe not on purpose, e.g. they could have been changing other things in the mickey mouse table designer and accidentally clicked and dragged a column up or down. Figuring out who and when will be guesswork unless you have auditing in place, or the ALTER or DROP/CREATE events are still in the default trace:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName, HostName, ApplicationName, StartTime, Action = CASE EventClass 
    WHEN 46 THEN 'Create' WHEN 47 THEN 'Drop' ELSE 'Alter' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (46,47,164)
AND EventSubClass = 1
AND DatabaseName = N'your database name'
AND ObjectName = N'your table name'
ORDER BY EndTime DESC;

The ordinal position for a column changed without my knowledge

1 Answers1