Click here to Skip to main content
Click here to Skip to main content

Tagged as

Determine Column Name

, 10 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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)

Share

About the Author

Corporal Agarn
Database Developer
United States United States
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 little VB.NET programming.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150123.1 | Last Updated 10 Oct 2011
Article Copyright 2011 by Corporal Agarn
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid