March 29, 2007 at 4:07 am
Just a general observation/question.
I am putting together some procedures to record schema changes over time. To this end I thought that the object id from sysobjects would be a good key to uniquely identify database objects. Everything works fine until getting to columns.
I can identify when a column is renamed, added, deleted, or the data type, length, precision etc is changed. But, if the column order is amended, then the table is apparently assigned a new id. I cannot think why this should be the case.
Any thoughts or observations gratefully received.
Having added the post I have now realised the reason. When a column is moved sql renames the original table, then creates a new table with the original name that has the columns in the new order. The data from the renamed table is then inserted to this new table. So a new id must be generated.
March 29, 2007 at 6:34 am
Hence another advantage of using t-sql to make ddl changes :
alter table dbo.tblname
alter column a int not null
You can't change the columns' order that way, but you don't ever need to anyways...
March 30, 2007 at 7:52 am
Also, if/when you upgrade to a new server, all the old ids will not mean anything. You should probably use the object name instead.
March 30, 2007 at 12:02 pm
probably someone use EM to change the table or used a script generated by EM.
It basicaly renames the old table, creates a newone, load the data, drops the old one and renames the new one to the old tablename. Hence the new objectid.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply