This website completely moved to new platform. For latest content, visit www.programmingposts.com

Search this Site

19 Aug 2013

Sql query to get common Columns in multiple tables in Sql Server

Lets suppose Database Name is MYDB and TABLE1, TABLE2, TABLE3 are tables in MYDB database

Sql query to retrieve all the column names in a table :

select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE1'

Sql query to get common Columns in two tables in Sql Server :

select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE1'
intersect
select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE2'

Sql query to get common Columns in three tables in Sql Server :


select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE1'
intersect
select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE2'
intersect
select COLUMN_NAME from MYDB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TABLE3'