CODEWITHSUNDEEP
sql-server-2008select
user1940864
user1940864

Reputation: 1

Select same column name in microsoft sql server from multiple tables

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.

Upvotes: 0

Views: 3606

Answers (3)

minakshi singh
minakshi singh

Reputation: 11

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

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

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.

Upvotes: 1

HMarioD
HMarioD

Reputation: 842

To get column names:

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

Upvotes: 0

Related Questions