Solved

SQL Server 2005 Stored Procedure Get Table Column Names

Posted on 2007-04-03
8
206 Views
Last Modified: 2010-03-19
Is there a way I can get the column names from a table in an SQL Server 2005 stored procedure.

ie.
Table_A
Column_A
Column_B
Column_C

Is there a function I can call on Table_A so I can use the column names.
0
Comment
Question by:Fraser_Admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18843148
check out the following view:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Table_A'
0
 

Author Comment

by:Fraser_Admin
ID: 18845212
Sorry, I guess I should have told you that it is a temporary table
#tmpTable.

This doesn't work for that.  I also tried ##tmpTable, but it didn't work either.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18845259
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Fraser_Admin
ID: 18845287
This does nothing, am I missing something???

use scales_fpnb
select *
into #tmpUnits
from
lookup_units

use tempdb
go

select *
from   INFORMATION_SCHEMA.TABLES
where  TABLE_CATALOG = 'tempdb'
and    TABLE_SCHEMA  = USER
and    TABLE_NAME = '#tmpUnits'
go

drop table #tmpUnits
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18845311
hmmm. it worked for me...
0
 

Author Comment

by:Fraser_Admin
ID: 18845353
ok I added a % to the end of #tmpUnits, and I see my temp table, but it is called #tmpUnits__________________________________

So based on that, how do i get the column names?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18845357

use scales_fpnb
select *
into #tmpUnits
from
lookup_units

use tempdb
go

select *
from   INFORMATION_SCHEMA.COLUMNS
where  TABLE_CATALOG = 'tempdb'
and    TABLE_SCHEMA  = USER
and    TABLE_NAME LIKE '#tmpUnits%'
go

drop table #tmpUnits
0
 

Author Comment

by:Fraser_Admin
ID: 18845476
That works perfect.  Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question