Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Determine Column Name

5.00/5 (1 vote)
10 Oct 2011CPOL 10.1K  
Using imported tables that may have a different column name to determine what it is.

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.


SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)