I had the same question and could not easily find any answers.
Here is what I came up with.
The sysobjects table stores each table name with a type = ‘u’
The syscolumns table stores the column name for each table.
Each of these tables shares an ID column, which you can use to join on.
This will allow you to join on any of your data tables and display the name of that table and the name of any of those table columns.
When you join on your second data table, you then specify which column name from your data table 1 equals the value from a column in your data table 2.
You will want to customize the select statement and enter your own table names, but the syntax looks something like this:
select so.name TableName, sc.name ColumnName, t1.*, t2.*
from [DBName].dbo.sysobjects so
join [DBName].dbo.syscolumns sc on so.id = sc.id and so.type = ‘u’
join [DBName].dbo.[table1] t1 on so.name = ‘[table1]’
join [DBName].dbo.[table2] t2 on t2.[columnName] = sc.name and sc.name = ‘[ColumnName]’
Note: table 1 is the table that has the column name that exists as a value in table 2.
Hopefully this makes sense.