Take the 2-minute tour ×

If it's possible to write a query with select

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 column which in table1.column name like table2.column name in like table3.column name

For example employee id maybe is a same column name in 3 tables or publisher id is a same in 3 tables

I can find the foreign key or primary key with diagram in my database but I want to write a query that show just columns which is a same title column name

Please don't ridicules me

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

To get column names:

select sc.name
from sysobjects so inner join syscolumns sc on so.id = sc.id
where so.name = 'tableName'
share|improve this answer
As of SQL Server 2005, it's better to use the new views in the sys schema, e.g. sys.columns and sys.tables –  marc_s Jan 1 '13 at 22:34
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. –  user1940864 Jan 2 '13 at 8:12
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. –  Tony Hopkinson Jan 2 '13 at 10:18
add comment

Your Answer


