1

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.

  • Can you give an example to explain it better ? What do you mean by column is out of order ? – Kin Shah Sep 4 '13 at 16:50
  • 1
    You (or someone) probably dropped the column and added it again: SQL Server 2008 R2 Add column into a specific location But why does it matter? Does your code depend on the order of columns? – ypercubeᵀᴹ Sep 4 '13 at 16:56
  • 2
    Please clarify the description. Particularly what does "not in terms of select statement" mean? You mean if you do SELECT * it is in the expected position? If so are you referring to ORDINAL_POSITION in INFORMATION_SCHEMA.COLUMNS then? – Martin Smith Sep 4 '13 at 16:59
6

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;
  • Wow - thanks for the prompt responses. Sorry, I'm a first-time poster and didn't expect such a quick turn-around. – Joe Hayes Sep 5 '13 at 14:48
  • Kin - yes, the columns are no "out of order", in the sense that they're in a different order than they were created. – Joe Hayes Sep 5 '13 at 14:51
  • Martin Smith - I think in trying to clarify, I made it more confusing :-). I don't mean the order that rows are returned in a select statement, I mean the order in which columns appear in the table definition. – Joe Hayes Sep 5 '13 at 14:52
  • Aaron - I think you're right. I've researched and researched this, and I can't find any other way it would happen. Unfortunately, the default trace has nothing on this. Oh, well. Guess how it happened will remain one of the world's great mysteries. Thanks. :-) – Joe Hayes Sep 5 '13 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.