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.
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.
ASKER
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.
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.
ASKER
yeah i got it. believe me..normally i am not this dumb.. :(
ASKER
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
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
ASKER
select
syscolumns.name
from
sysobjects join syscolumns
on sysobjects.id = syscolumns.id
where sysobjects.name = tbljobs
Invalid column name 'tbljobs'
syscolumns.name
from
sysobjects join syscolumns
on sysobjects.id = syscolumns.id
where sysobjects.name = tbljobs
Invalid column name 'tbljobs'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.