Link to home
Start Free TrialLog in
Avatar of newoffice
newoffice

asked on

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.
Avatar of mccarIT
mccarIT

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.
Avatar of newoffice

ASKER

what is sc?
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.
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.
yeah i got it. believe me..normally i am not this dumb.. :(
The column prefix 'syscolumns' does not match with a table name or alias name used in the query.
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

select
    syscolumns.name
from  
    sysobjects join syscolumns
        on sysobjects.id = syscolumns.id
where sysobjects.name = tbljobs

Invalid column name 'tbljobs'
ASKER CERTIFIED SOLUTION
Avatar of mccarIT
mccarIT

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial