Solved

PROC to return table field names

Posted on 2014-11-23
9
232 Views
Last Modified: 2014-11-23
Table1        <-- starting with a table like this
===============================
Field1   Field2    Field3       etc.
-------    --------      ---------
x             x              x
x             x              x
x             x              x

The date in this table to be ignored. The return expected is:

FieldNames
============
Field1
Field2
Field3
etc.


Question: How to write a Proc or function to return the field names in Table1 as shown above?
0
Comment
Question by:Mike Eghtebas
  • 4
  • 4
9 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 40461367
Use INFORMATION_SCHEMA.COLUMNS system view for this:
@sql = 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @YourTable + 
''' order by ORDINAL_POSITION'
EXEC @sql

Open in new window

0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461376
wonderful. Is it a good idea to make a function or a proc, performance-wise?
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461382
Depends on what you plan to do with it.
Functions can be used in-line in select statements, procedures cannot. But procedures perform better.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461386
performance-wise?
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461394
Like I said, procedures perform better. They're faster than functions.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40461407
Koen Van Wielink,
Like I said, procedures perform better.
Sorry, my bad.

chaau,
I ran this PROC, I got the following error::
Msg 2812, Level 16, State 62, Procedure spGetFieldNames, Line 14
Could not find stored procedure 'select COLUMN_NAME
from information_schema.columns
where table name = 'Search' order by ORDINAL_POSITION'.



ALTER PROCEDURE  [dbo].[spGetFieldNames]
@TableName varchar(100)

As
Begin
 
Declare @sql varchar(max);

-- I tried Select also
 Set @sql= 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
exec @sql

end

Open in new window

0
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 250 total points
ID: 40461416
There's an underscore missing in the where clause:

where table_name = ''' + @TableName + 

Open in new window


When working with dynamic SQL, try to print your generated statements before executing them. If you get an error, you can copy/paste the printed statement to see where things go wrong.

declare @TableName varchar(100)

select @TableName = 'WorkOrder'
 
Declare @sql varchar(max);

-- I tried Select also
 Set @sql= 'select COLUMN_NAME 
from information_schema.columns 
where table name = ''' + @TableName + 
''' order by ORDINAL_POSITION'
--exec @sql
print @sql

Open in new window

0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 40461418
Oh, and you need to place () around the @sql variable in the exec statement:

exec (@sql)

Open in new window

0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40461427
Thank you.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

831 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