Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2005 Stored Procedure Get Table Column Names

Posted on 2007-04-03
8
Medium Priority
?
212 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

972 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