?
Solved

Get the list of field name with comma sepeartion from a SQL 2005 table.

Posted on 2007-07-26
9
Medium Priority
?
234 Views
Last Modified: 2012-06-27
Get the list of field name with comma sepeartion from a SQL 2005 table.
0
Comment
Question by:newoffice
  • 5
  • 4
9 Comments
 
LVL 1

Expert Comment

by:mccarIT
ID: 19577598
This will get all columns in a table:

select
    sc.name
from  
    sysobjects so
    join syscolumns sc
        on so.id = sc.id
where so.name = tblJob

There is a way to return them in a comma list if necessary but it is a little more involved.  For what you are doing you would be better off copying it from the query results and adding the commas manually.
0
 
LVL 1

Author Comment

by:newoffice
ID: 19577634
what is sc?
0
 
LVL 1

Expert Comment

by:mccarIT
ID: 19577695
laziness.  so and sc are arbitrary abbreviations for the tables sysobjects and syscolumns.  All it does is prevents me from having to retype the full table names in the "on" clause of the join.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 1

Author Comment

by:newoffice
ID: 19577699
select
    sysscolumns.name
from  
    sysobjects join syscolumns sc
        on sysobjects.id = syscolumns.id
where sysobjects.name = tblJobs

The column prefix 'syscolumns' does not match with a table name or alias name used in the query.
0
 
LVL 1

Author Comment

by:newoffice
ID: 19577706
yeah i got it. believe me..normally i am not this dumb.. :(
0
 
LVL 1

Author Comment

by:newoffice
ID: 19577887
The column prefix 'syscolumns' does not match with a table name or alias name used in the query.
0
 
LVL 1

Expert Comment

by:mccarIT
ID: 19577906
you specified the abbreviation sc and then used the full name in the 'on' clause.  Either remove the alias from the join line or use the alias in the 'on' line.

select
    sysscolumns.name
from  
    sysobjects join syscolumns sc
        on sysobjects.id = sc.id
where sysobjects.name = tblJobs

OR

select
    sysscolumns.name
from  
    sysobjects join syscolumns
        on sysobjects.id = syscolumns.id
where sysobjects.name = tblJobs

0
 
LVL 1

Author Comment

by:newoffice
ID: 19578249
select
    syscolumns.name
from  
    sysobjects join syscolumns
        on sysobjects.id = syscolumns.id
where sysobjects.name = tbljobs

Invalid column name 'tbljobs'
0
 
LVL 1

Accepted Solution

by:
mccarIT earned 2000 total points
ID: 19578928
you need to put single quotes around the table name.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 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