When I opened my mail today, I got an email from Rajkumar on Aug 13 2011 and the content is,
“Hi Varun,
Good Evening…
I am Rajkumar.D from chennai when i searching for some query to find the mismatch in table i found your blog and found the solution from your blog and solved the issue thanks for that. Now i have two questions in sql .
1.How to find columns mismatch or columns missed in a table.
2.How to compare columns mismatch between two tables.
awaiting for your reply.
With regards,
Rajkumar.D
MILES TO GO……….”
Since I am busy with my weekend, I was able to open my email only today.
Here I am explaining with two Databases TestDb1 and TestDb2. In each DB, there is a table Employeedetails in which TestDB2.Employeedetails is missing a column Department from TestDB1.Employeedetails.
/****** Object: Table [dbo].[EmployeeDetails] Script Date: 08/16/2011 08:35:21 ******/
CREATE TABLE [dbo].[EmployeeDetails]
(
[ID] [INT] NOT NULL,
[EmpName] [VARCHAR](50) NULL,
[Department] [VARCHAR](50) NULL,
CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]
GO
USE [TestDB2]
GO
/****** Object: Table [dbo].[EmployeeDetails] Script Date: 08/16/2011 08:36:16 ******/
CREATE TABLE [dbo].[EmployeeDetails]
(
[ID] [INT] NOT NULL,
[EmpName] [VARCHAR](50) NULL,
CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]
GO
Here is the script to identify the mismatch of the columns between two tables.
USE testdb1
GO
--1.How to find columns mismatch or columns missed in a table.
--2.How to compare columns mismatch between two tables.
SELECT db1.table_name,
db1.column_name,
‘Missing in TestDb2…!’ AS details
FROM testdb1.information_schema.columns AS db1
LEFT JOIN testdb2.information_schema.columns AS db2
ON db1.table_name = db2.table_name
AND db1.column_name = db2.column_name
WHERE db2.column_name IS NULL
Modify the script as per the need.
Bharath
13 years agoWearing the critical hat here, (please remove comment if you dont like it), what is the need of this query. Will somebody actually use this in an application ? They shouldn’t interrogate table structure during the running of an application, nobecause it may have performance impact, just because it is not ‘database’ ethical.
varun
13 years agoHi Bharath, Thanks for your comments, but there is no Performance issues in this query, because we are collecting only views which contain metadata of the table. This query may help in the scenarios where you are moving one DB Architecture to another.It is not purely used in the an application.
swetha
13 years agohai varun,
will u help me out in this issue
i have two tables with many columns in each. i want to know whether common column exists in both tables or not ( to perform join operation).It takes time to do manually.so i want to that by using query or some command ( in sql). I Tried the below query but it is not working
SELECT COLUMN_NAME
FROM all_TAB_COLUMNS
WHERE table_name = ’emp’
intersect
SELECT COLUMN_NAME
FROM all_TAB_COLUMNS
WHERE table_name = ‘hai’;
Varun R
13 years agoHi Swetha,
Please look the below query,
USE AdventureWorksLT2008
GO
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘Customer’
and TABLE_SCHEMA = ‘SalesLT’
intersect
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘CustomerAddress’
and TABLE_SCHEMA = ‘SalesLT’
Hope this will help you….!