-- Create test tables
CREATE TABLE employee
(
	e_ID int identity(1,1) NOT NULL,
	e_name varchar(20) NOT NULL,
	e_phone varchar(20) NULL,
	e_phone2 varchar(20) NULL,	-- Missing from "_dup"
	PRIMARY KEY
	(
		e_ID
	)
)
GO
CREATE TABLE employee_dup
(
	idn	int NOT NULL,
	e_ID int identity(1,1) NOT NULL,
	e_name varchar(20) NOT NULL,
	e_phone varchar(30) NULL,	-- Larger than original table
	e_phone3 varchar(20) NULL,	-- Missing from original table
	PRIMARY KEY
	(
		e_ID
	)
)
GO

SELECT	[Table] = T1_name,
	[Msg] = CASE WHEN C1_object_id IS NULL THEN 'Missing1'
			WHEN C2_object_id IS NULL THEN 'Missing2'
			ELSE 'Different'
			END,
	[Column] = C1_name,
	[Type1] = TY1.name,	
	[*] = CASE WHEN COALESCE(TY1.system_type_id, -1) <> COALESCE(TY2.system_type_id, -1) THEN '*' ELSE '' END,
	[Type2] = TY2.name,	
	[max_length1] = C1_max_length,	
	[*] = CASE WHEN COALESCE(C1_max_length, -1) <> COALESCE(C2_max_length, -1) THEN '*' ELSE '' END,
	[max_length2] = C2_max_length,	
	[precision1] = C1_precision,	
	[*] = CASE WHEN COALESCE(C1_precision, -1) <> COALESCE(C2_precision, -1) THEN '*' ELSE '' END,
	[precision2] = C2_precision,	
	[scale1] = C1_scale,	
	[*] = CASE WHEN COALESCE(C1_scale, -1) <> COALESCE(C2_scale, -1) THEN '*' ELSE '' END,
	[scale2] = C2_scale,	
	[collation_name1] = C1_collation_name,	
	[*] = CASE WHEN COALESCE(C1_collation_name, '') <> COALESCE(C2_collation_name, '') THEN '*' ELSE '' END,
	[collation_name2] = C2_collation_name
FROM
(
	SELECT	[T1_name] = T1.name,
		[C1_name] = C1.name,
		[C1_object_id] = C1.object_id,
		[C2_object_id] = C2.object_id,
		[C1_column_id] = C1.column_id,
		[C1_system_type_id] = C1.system_type_id,
		[C2_system_type_id] = C2.system_type_id,
		[C1_max_length] = C1.max_length,
		[C2_max_length] = C2.max_length,
		[C1_precision] = C1.precision,
		[C2_precision] = C2.precision,
		[C1_scale] = C1.scale,
		[C2_scale] = C2.scale,
		[C1_collation_name] = C1.collation_name,
		[C2_collation_name] = C2.collation_name
	FROM	sys.tables AS T1
		JOIN sys.tables AS T2
			 ON T2.name = T1.name + '_dup'
		JOIN sys.columns AS C1
			 ON C1.object_id = T1.object_id
		LEFT OUTER JOIN sys.columns AS C2
			 ON C2.object_id = T2.object_id
			AND C2.name = C1.name
	UNION ALL
	SELECT	[T1_name] = T1.name,
		[C1_name] = COALESCE(C1.name, C2.name),
		[C1_object_id] = C1.object_id,
		[C2_object_id] = C2.object_id,
		[C1_column_id] = COALESCE(C1.column_id, C2.column_id),
		[C1_system_type_id] = C1.system_type_id,
		[C2_system_type_id] = C2.system_type_id,
		[C1_max_length] = C1.max_length,
		[C2_max_length] = C2.max_length,
		[C1_precision] = C1.precision,
		[C2_precision] = C2.precision,
		[C1_scale] = C1.scale,
		[C2_scale] = C2.scale,
		[C1_collation_name] = C1.collation_name,
		[C2_collation_name] = C2.collation_name
	FROM	sys.tables AS T1
		JOIN sys.tables AS T2
			 ON T2.name = T1.name + '_dup'
		JOIN sys.columns AS C2
			 ON C2.object_id = T2.object_id
		LEFT OUTER JOIN sys.columns AS C1
			 ON C1.object_id = T1.object_id
			AND C1.name = C2.name
	WHERE	    C1.object_id IS NULL
		AND C2.name <> 'idn'	-- Ignore column "IDN" only in "_DUP"
) AS X
	LEFT OUTER JOIN sys.types AS TY1
		 ON TY1.system_type_id = C1_system_type_id
	LEFT OUTER JOIN sys.types AS TY2
		 ON TY2.system_type_id = C2_system_type_id
WHERE	   C1_object_id IS NULL	-- Column does not exist in main table
	OR C2_object_id IS NULL	-- Column does not exist in "_dup" table
	OR C1_system_type_id <> C2_system_type_id
	OR C1_max_length <> C2_max_length
	OR C1_precision <> C2_precision
	OR C1_scale <> C2_scale
	OR (C1_collation_name <> C2_collation_name
		 OR (C1_collation_name IS NULL AND C2_collation_name IS NOT NULL)
		 OR (C1_collation_name IS NOT NULL AND C2_collation_name IS NULL)
	)
--
ORDER BY T1_name, C1_column_id, C1_name

quote:

---

Originally posted by gautham.gn

I want a query showing the result set as table name_dup and column name which is not present in main table. Btw., this is the requirement,so I want this in a form of query.

---

SELECT c.TABLE_NAME + '_dup' AS tablename,
c.COLUMN_NAME AS columnname
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME NOT LIKE '%_dup'
AND NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME  = c.TABLE_NAME + '_dup'
AND COLUMN_NAME = c.COLUMN_NAME
)