Determine Column Name

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
-- Create a temporary table (shell only) to be used in finding column name
-- @Tname is from the loop
SET @strSQL = 'SELECT * INTO ##CampaignHeaders '
    + 'FROM [LinkedServer].[Dialer].[dbo].' + @Tname
    + ' WHERE 1 = 2 '
EXECUTE (@strSQL)

-- Determine if the column name 
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.