Solved

SQL Server 2005 Stored Procedure Get Table Column Names

Posted on 2007-04-03
8
205 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

756 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