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 (https://blog.sqlauthority.com)
8 Comments. Leave new
First let me start with the database model. Why would you want to put your Archive tables in the same database. Ideal solution would be to have a separate database that contains all tables containing archived data and have an scheduled ETL process that archives data weekly/monthly/quaterly….., UNLESS you have an application that reads this archived data which cannot make cross database connections.
Now, lets talk about the comparing objects.
The script which Pinal has shared in this blog will let you start but is providing only 1% information of what you need, like the script gives you only column names, but not the data types, constraints, indexes, keys, references that could have been defined on those column.
What I would do is to use SQL Compare tools (Ofcource that are free) with which you can compare two databases (Yes, I said two different databases, what is the point in comparing the same databases against itself). Since you have all your objects in the same database, you would have to some setup, before you use this tool,
1. First separate objects in different database. Have all your real tables in one database and script out all your Archived tables in your new database say Archive database. (This is just for the sake of testing), (DON’T DROP ANY TABLE IN PRODUCTION, DO THIS TEST IN DEVELOPMENT).
2. Step 2 would be to rename the table names in the new Archived database to match the real names of tables in the main database. (DON’T RENAME ANY TABLE IN PRODUCTION, DO THIS TEST IN DEVELOPMENT).
Now run DB Compare tool, This is a free tool with lots and lots of flexibility. Download this tool and test it in development, at the end of comparison this tool generates a script to synchronize destination database with source. I have used it multiple times and find this useful.
Download this tool here (sqldbtools (.) com)
You can also use SQL Server’s CodePlex version of DB compare tool (My personal advice, use the sqldbtools and stay away from CodePlex).
Download this tool at below link,
Hope this helps, I am sure there will be some more interesting comments coming in….
~ IM.
One more soltuion.
–For Unique Columns
Select B.[name] as [Column Name] from sys.tables A
Inner Join sys.columns B
On A.[object_id]=B.[object_id]
Where A.[Name] In (‘ArchiveTable’,’CurrentTable’)
Group By B.[name]
Having Count(*)=1
–For Duplicate Columns
Select B.[name] as [Column Name] from sys.tables A
Inner Join sys.columns B
On A.[object_id]=B.[object_id]
Where A.[Name] In (‘ArchiveTable’,’CurrentTable’)
Group By B.[name]
Having Count(*)=2
Similar solution, but give combined result with table name.
SELECT OBJECT_NAME(OBJECT_ID) TableName,name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) IN (‘ArchiveTable’, ‘CurrentTable’)
and name not in (
SELECT name ColumnName
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID) IN( ‘ArchiveTable’ ,’CurrentTable’)
GROUP By name
HAVING COUNT(*)=2)
Is there any way from that we can determine when there is a change in column type (either in datatype or in length)?
Like Archive table has a column Col1 Varchar(50) and Main table has a column Col1 Varchar(100)
SELECT A.Column_Name,A.DATA_TYPE ‘Archive DataType’,A.CHARACTER_MAXIMUM_LENGTH ‘Archive Length’,
B.DATA_TYPE ‘Current DataType’,B.CHARACTER_MAXIMUM_LENGTH ‘Current Length’
FROM INFORMATION_SCHEMA.COLUMNS A inner join INFORMATION_SCHEMA.COLUMNS B on A.COLUMN_NAME= B.COLUMN_NAME
WHERE A.TABLE_NAME =’ArchiveTable’ AND B.TABLE_NAME = ‘CurrentTable’
AND (A.DATA_TYPEB.DATA_TYPE OR A.CHARACTER_MAXIMUM_LENGTH B.CHARACTER_MAXIMUM_LENGTH)
Hi..M a beginner in sql..i need help plz..can u plz tell me the best way to study about SQL server implementation,maintanence and back up..
Also from where can i get a detailed description about difference in various SQL server