Select same column name in microsoft sql server from multiple tables

Question

Is it possible to write a query:

SELECT * from table1, table 2, table 3 ...

where table1.column name like table2.column name like table3.column name

We don't know the name of our column, we say any of our columns which are in table1.column name like table2.column name in like table3.column name

For example: employee id maybe is a column that is common to 3 tables, or publisher id is a column that is common to 3 tables.

I can find the foreign key or primary key with a diagram in my database, but I want to write a query that shows just columns which have a same title as column name.

Tony Hopkinson · Accepted Answer · 2013-01-01 19:42:21Z

1

No clue as to why you are doing this, so a bit hard to steer, but there are a number of tables and views in the database that describe the database.

Information_Schema.Columns is probably where you should start. Note this is meta data, if you want the content of the actual tables you'll have to go a step further, and build some dynamic sql, or equivalent.

Select Column_Name,Count(*) As NumberOfOccurences From Information_Schema.Columns will give you the column names and how often you've used them for instance.

answered Jan 1, 2013 at 19:42

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

minakshi singh · Accepted Answer · 2020-02-14 12:00:38Z

1

If you know the column name and want to know in which different table same column name is used then write SQL query as

SELECT schema_name(t.[schema_id]) + '.' + t.[name] AS Tables FROM sys.tables t INNER JOIN sys.columns c on t.[object_id] = c.[object_id] WHERE c.[name] = 'Your_Common_Column_Name'

You will get all the table list with their respective schema

answered Feb 14, 2020 at 12:00

minakshi singh

111 silver badge3 bronze badges

Comments

HMarioD · Accepted Answer · 2013-01-01 19:40:07Z

0

To get column names:

select sc.name
from sysobjects so inner join syscolumns sc on so.id = sc.id
where so.name = 'tableName'

answered Jan 1, 2013 at 19:40

HMarioD

84210 silver badges18 bronze badges

3 Comments

marc_s Over a year ago

As of SQL Server 2005, it's better to use the new views in the sys schema, e.g. sys.columns and sys.tables

user1940864 Over a year ago

thank you HMariD,your syntax show all column name in one table,but my question is just show the same column name in 3 table with their data. something like that union.

Tony Hopkinson Over a year ago

user1940864. You have to construct the sql to do something like that e.g. Exec('Select * From ' + @TableName) where you've got TableName from some query on the schema.

Collectives™ on Stack Overflow

Select same column name in microsoft sql server from multiple tables

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related