Compare column names between two tables in SQL Server

PRA
 
on Jul 07, 2021 05:27 AM
440 Views

Hi!

I have two tables. I want compare two table and show difference columns name. 

CREATE TABLE [dbo].[tblOne](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](10) NULL,
    [address] [nchar](10) NULL,
    [dob] [date] NULL
) 
CREATE TABLE [dbo].[tblTwo](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [lname] [nchar](10) NULL,
    [name] [nchar](10) NULL,
    [dob] [date] NULL,
    [address] [nchar](10) NULL,
    [phone] [int] NULL,
    [age] [int] NULL
)
select * from tblOne
minus
select * from tblTwo

I want show in result lname, phone and age because in three columns haven't on tblOne.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 07, 2021 05:46 AM

Hi PRA,

Use below query.

SELECT A.Column_Name
FROM Information_Schema.Columns A
FULL OUTER JOIN Information_Schema.Columns B ON A.Column_Name = B.Column_Name
	AND B.Table_Schema = 'dbo' AND B.Table_Name = 'tblOne'
WHERE A.Table_Schema = 'dbo' AND A.Table_Name = 'tblTwo'
	AND (A.Column_Name IS NULL OR B.Column_Name IS NULL)