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.

3
  • Can you give an example to explain it better ? What do you mean by column is out of order ?
    – Kin Shah
    Commented Sep 4, 2013 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? Commented Sep 4, 2013 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? Commented Sep 4, 2013 at 16:59

1 Answer 1

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;
4
  • Wow - thanks for the prompt responses. Sorry, I'm a first-time poster and didn't expect such a quick turn-around.
    – Joe Hayes
    Commented Sep 5, 2013 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
    Commented Sep 5, 2013 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
    Commented Sep 5, 2013 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
    Commented Sep 5, 2013 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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