I have mentioned earlier on this blog that I love social media – Facebook and Twitter. I receive so many interesting questions that sometimes I wonder how come I never faced them in my real life scenario. Well, let us see one of the similar situation. Here is one of the questions which I received on my social media handle.
“Pinal,
I have a large database. I did not develop this database but I have inherited this database. In our database we have many tables but all the tables are in pairs. We have one archive table and one current table. Now here is interesting situation. For a while due to some reason our organization has stopped paying attention to archive data. We did not archive anything for a while. If this was not enough we even changed the schema of current table but did not change the corresponding archive table. This is now becoming a huge huge problem. We know for sure that in current table we have added few column but we do not know which ones.
Is there any way we can figure out what are the new column added in the current table and does not exist in the archive tables? We cannot use any third party tool. Would you please guide us?”
Well here is the interesting example of how we can use sys.column catalogue views and get the details of the newly added column. I have previously written about EXCEPT over here which is very similar to MINUS of Oracle.
In following example we are going to create two tables. One of the tables has extra column. In our resultset we will get the name of the extra column as we are comparing the catalogue view of the column name.
USE AdventureWorks2012
GO
CREATE TABLE ArchiveTable (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(100), Col3 VARCHAR(100));
CREATE TABLE CurrentTable (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(100), Col3 VARCHAR(100), ExtraCol INT);
GO
-- Columns in ArchiveTable but not in CurrentTable
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable'
EXCEPT
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable'
GO
-- Columns in CurrentTable but not in ArchiveTable
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'CurrentTable'
EXCEPT
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) = 'ArchiveTable'
GO
DROP TABLE ArchiveTable;
DROP TABLE CurrentTable;
GO
The above query will return us following result.
I hope this solves the problems. It is not the most elegant solution ever possible but it works. Here is the puzzle back to you – what native T-SQL solution would you have provided in this situation?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology
