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

Get the list of field name with comma sepeartion from a SQL 2005 table.
LVL 1
newofficeAsked:
Who is Participating?
 
mccarITConnect With a Mentor Commented:
you need to put single quotes around the table name.
0
 
mccarITCommented:
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
 
newofficeAuthor Commented:
what is sc?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
mccarITCommented:
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
 
newofficeAuthor Commented:
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
 
newofficeAuthor Commented:
yeah i got it. believe me..normally i am not this dumb.. :(
0
 
newofficeAuthor Commented:
The column prefix 'syscolumns' does not match with a table name or alias name used in the query.
0
 
mccarITCommented:
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
 
newofficeAuthor Commented:
select
    syscolumns.name
from  
    sysobjects join syscolumns
        on sysobjects.id = syscolumns.id
where sysobjects.name = tbljobs

Invalid column name 'tbljobs'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.