How to get Renamed, Deleted, and Added Columns of the Table?
-
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table. Please reply ASAP Thanks.
-
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table. Please reply ASAP Thanks.
You will need to build a schema compare utility. Make a connection to each of the databases you want to compare and you will have to walk through each one of the tables and compare the column definitions. Look at sys.columns http://msdn.microsoft.com/en-us/library/ms176106.aspx[^]
-
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table. Please reply ASAP Thanks.
You attend project meetings where the DBA tells you.
-
You will need to build a schema compare utility. Make a connection to each of the databases you want to compare and you will have to walk through each one of the tables and compare the column definitions. Look at sys.columns http://msdn.microsoft.com/en-us/library/ms176106.aspx[^]
Then you need to add a ton of artificial intelligence and still guess wrong.
-
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table. Please reply ASAP Thanks.
I agree with PIEBALD; you do not know why the columns are altered, so best idea is to ignore the changes, and declare "incompatibility with the current version" if they're not as you expected them to be. If a column is added, it's not always desired to have it visible; if it is visible, it will have consequences for the layout, which means some changes to reports. If a column is deleted, it's not always desired to ignore it; it could have been renamed during an update, with the intention of renaming it back later - your behaviour would hide a very important bug.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
Then you need to add a ton of artificial intelligence and still guess wrong.
I disagree with the comment about "artificial intelligence" and "guessing". The question was How I will get the added, deleted or renamed column of the Table. My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'. There is no reason to be sarcastic when replying. Just stay professional. Now let me get my flak jacket and fireman coat before the responses come flying in. :)
-
I disagree with the comment about "artificial intelligence" and "guessing". The question was How I will get the added, deleted or renamed column of the Table. My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'. There is no reason to be sarcastic when replying. Just stay professional. Now let me get my flak jacket and fireman coat before the responses come flying in. :)
And my point is that seeing the differences in the schema won't show you how you got there, what changes were made. For example, if A,B,C changed to A,D,C -- Was B renamed to D? Was B deleted and D added? There is no way to know with the information given. You may also look at the datatype, but you may also need to investigate the contents of the columns to see whether or not D contains the same contents as B did. In such a scenario (and based on some data I'm working on now) maybe the original was
TRANSACTION_D_T VARCHAR(20)(in yyyy-MM-dd HH:MM:ss format) and it was changed toTRANSACTION_DATE DATETIME-- how would you determine what change was made simply by looking at the schema? -
Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country) or deleted the column(City) or rename the column (Name to FirstName). Now, how I will get the added, deleted or renamed column of the Table. Please reply ASAP Thanks.
I want to know whether it contains data.If not ,it's easy.
-
I want to know whether it contains data.If not ,it's easy.
Really?
-
I disagree with the comment about "artificial intelligence" and "guessing". The question was How I will get the added, deleted or renamed column of the Table. My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'. There is no reason to be sarcastic when replying. Just stay professional. Now let me get my flak jacket and fireman coat before the responses come flying in. :)
David Mujica wrote:
My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'.
Good point; yet there are certain dangers to the approach, which should also be mentioned if one is to make an informed decision. It's good to see a broad variety of answers, each with their own motivation. You're right that my comment was superfluous after the warning of PIEBALD, and reading back it indeed looks a tad patronizing.
David Mujica wrote:
There is no reason to be sarcastic when replying. Just stay professional.
..will try, a little bit harder. Then again, sarcasm is an efficient way to get a point across and to create a few seconds of shock in which one contemplates the previously ignored point.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
David Mujica wrote:
My response gave a viable option for him to determine the difference between tables in two databases. I did not try to read into the 'why' or 'what for'.
Good point; yet there are certain dangers to the approach, which should also be mentioned if one is to make an informed decision. It's good to see a broad variety of answers, each with their own motivation. You're right that my comment was superfluous after the warning of PIEBALD, and reading back it indeed looks a tad patronizing.
David Mujica wrote:
There is no reason to be sarcastic when replying. Just stay professional.
..will try, a little bit harder. Then again, sarcasm is an efficient way to get a point across and to create a few seconds of shock in which one contemplates the previously ignored point.
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
I always appreciate the help I get from the folks here. Sometimes I have to resist jumping on the dog-pile. There are many posts that I just don't respond to because they are just not worth my time. Thanks again for your (and others) contribution to the community; I've learned a lot here. David
-
And my point is that seeing the differences in the schema won't show you how you got there, what changes were made. For example, if A,B,C changed to A,D,C -- Was B renamed to D? Was B deleted and D added? There is no way to know with the information given. You may also look at the datatype, but you may also need to investigate the contents of the columns to see whether or not D contains the same contents as B did. In such a scenario (and based on some data I'm working on now) maybe the original was
TRANSACTION_D_T VARCHAR(20)(in yyyy-MM-dd HH:MM:ss format) and it was changed toTRANSACTION_DATE DATETIME-- how would you determine what change was made simply by looking at the schema?I agree that seeing a difference in a schema does not tell you how you got there, but the simple matter of detecting a change will initiate an investigation. No change = No problem (ideally) Change = Investigation When there is team development on an application there must be communication among the team to reflect any changes. Using a source control system like Subversion is a good way to see changes made and designating a single person as the responsible party for making any changes to the schema is also a good idea.