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 '
SELECT TOP 1 @strColNam = Column_Name
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.
I have been a programmer and DBA for many years. I have written programs in BASIC, VB, VBA, VB.NET, Fortran, T-SQL, and PL-SQL. I have worked with FoxBase, FoxPro, Visual FoxPro, Oracle and MS SQL Server.
Currently I spend most of my time in MS SQL Server along with a very little VB.NET programming.