Solved

PROC to return table field names

Posted on 2014-11-23
9
224 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 33

Author Comment

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

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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40461386
performance-wise?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

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

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 12

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 12

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 33

Author Closing Comment

by:Mike Eghtebas
ID: 40461427
Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now