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

Determine Column Name

By , 10 Oct 2011
Rate this:
Please Sign up or sign in to vote.

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)

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 | Mobile
Web04 | 2.8.140415.2 | Last Updated 10 Oct 2011
Article Copyright 2011 by Corporal Agarn
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid