Sql server get the creation date column
-
Do a simple test : 'Create table test ( A int null, B int null, C int null );'
On sql server GUI , modify your table :
Insert a column X between A and B column.
Now execute this query :
'SELECT * from sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id WHERE obj.Name='test' ORDER BY obj.create_date'
Normally X column must be the last , and this column is in the seconde position. Is it a bugg ?This obj.create_date is not correct , why ?
-
You're ordering your results by the create date of the table. Since that will be the same for each column in the table, you can't guarantee that your results will be returned in any particular order.
John
-
Thanks John ,
Is it possible to sort by creation date of the column ?
-
Not as far as I know - there is no create_date column in sys.columns. I would advise against using the GUI to insert a new column between two existing columns. I think that it creates a new table with the new column and then copies the data over from the old table - potentially an expensive operation for a large table. I would advise using the syntax here http://msdn.microsoft.com/en-us/library/ms190273(v=SQL.90).aspx to add your column - you can then order your results by column_id.
John
-
There's no date column in that table. The best you can do afaik is to order by column_id which is like an identity column for each object in that table.
This assume you don't use the GUI to edit your tables. Which, possibly, would drop and rebuild the table making that info useless.
-
John Mitchell-245523 (11/1/2011)
I would advise using the GUI to insert a new column between two existing columns.Did I miss something in the conversation?
Edit YUP.
There's no point in putting the columns in the correct order. You do that in the select statements.
SQL will order those columns on the pages as it pleases so you have no control over that either.
-
Ninja's_RGR'us (11/1/2011)
Did I miss something in the conversation?Edit YUP.
There's no point in putting the columns in the correct order. You do that in the select statements.
SQL will order those columns on the pages as it pleases so you have no control over that either.
Yes, sorry about that - the firewall software (or whatever it is here) bans certain phrases, so I had to add my post bit by bit to work out what was being objected to. Turns out it was "?LTER ?ABLE".
Edit - just realised that I missed the word "against" from my original post, just to add to the confusion even further!
John
-
No in the OP, I had misread that phrase => Insert a column X between A and B column.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply