While pulling data from a third party linked database, I found that the column name I was interested in could be one of two values. Since I could not directly query the table for column names, I created a temporary table that I could query the Information_schema.columns table for the name.
The way the third party software is setup, it creates tables based on user input. These tables can have any name the user wishes, so I have code to find the tables in question then loop through them getting the information I need. Because I know the two column names available, I can look at a table and find the column name.
DECLARE @strColNam VARCHAR(50) = 'NOName'
IF OBJECT_ID (N'tempdb..##CampaignHeaders', N'U') IS NOT NULL
DROP TABLE ##CampaignHeaders
SET @strSQL = 'SELECT * INTO ##CampaignHeaders '
+ 'FROM [LinkedServer].[Dialer].[dbo].' + @Tname
+ ' WHERE 1 = 2 '
EXECUTE (@strSQL)
SELECT TOP 1 @strColNam = Column_Name
FROM tempdb.information_schema.columns
WHERE OBJECT_ID (N'tempdb..##CampaignHeaders', N'U') IS NOT NULL
AND Column_Name IN ('Posible1', 'Posible2')
I can now use the value of @strColNam
in dynamic code to get the needed information.