This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few scripts for getting the column differences of the similar table in SQL Server.
If your table has a number of columns like more than 100, it is very difficult to compare the column with another same table.
Mostly, we are doing this kind of comparison between production database and other sprint databases.
You can access the below small demonstration on this:
Create two sample tables:
|
1 2 3 4 |
CREATE TABLE tbl_ABC (Rno INT, Name CHAR(20), Class INT) GO CREATE TABLE tbl_XYZ (Rno INT, Name VARCHAR(20)) GO |
Find the difference of column datatype:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select ic1.TABLE_NAME ,ic1.COLUMN_NAME ,ic1.DATA_TYPE ,ic2.TABLE_NAME ,ic2.DATA_TYPE ,ic2.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS as ic1 left join INFORMATION_SCHEMA.COLUMNS as ic2 on ic1.COLUMN_NAME=ic2.COLUMN_NAME where ic1.TABLE_NAME='tbl_ABC' and ic2.TABLE_NAME='tbl_XYZ' and ic1.data_type <> ic2.DATA_TYPE |
Result:
Find extra column from another similar table:
|
1 2 3 4 5 6 7 8 9 |
select ic2.TABLE_NAME ,ic2.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS ic2 where table_name='tbl_ABC' and ic2.COLUMN_NAME not in (select column_name from INFORMATION_SCHEMA.COLUMNS where table_name='tbl_XYZ') |
Result:
Leave a Reply