Extracting Column Names for a Table in SQL as a CSV String

I had a need (creating CSV BCP files) that required knowing the column names of a MS SQL table in the order they were created. Some inspiration from Mark Clerget and a little fooling around with SQL Query Analyzer resulted in the following.

DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''

SET @t='authors'

SELECT @c = @c + c.name + ', '
    FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id

    WHERE o.name = @t
    ORDER BY colid

SELECT Substring(@c, 1, Datalength(@c) - 2)

Which gives the following result:

au_id, au_lname, au_fname, phone, address, city, state, zip, contract

Perfect. Note the use of @c = @c + c.name in the select clause to colapse 9 rows into 1 row. I've used this technique many times in the past to generate a single string from multiple rows without resorting to cursors.

Posted Under Programming on 02.18.2005

Trackback address for this post

Trackback URL (right click and copy shortcut/link location)

7 comments

Comment from: Haacked [Visitor]

I like that technique as well. Once in an interview with very defensive and antagonistic interviewers (they didn't think I was worth the rate I quoted), they asked me how I'd select a comma separated list of names from a users table. I knew they were looking for a cursor solution. I wrote the one you presented and they gleefully told me I was wrong.

I didn't get the gig, but did write their manager an email stating that they're interview process was flawed and that my solution was 1000 times faster than theirs.

Yeah, I was a little punk.

02/18/05 @ 10:07

Comment from: mclerget [Member]

Very cool Haacked. Well, not cool that the interviewers didn't get it, but that you stood your ground. I think its pretty clear that would NOT have been happy there anyway.

The saying...."In the land of the blind, the one-eyed man is King" applies to your experience. So maybe a little rewording..."In the land SQL wannabe interviewer, the CURSOR approach is King!". Anyway, you inspired me to share a similar experience that I think many of us have encountered about the root causes of project success and failure. I'll be blogging it soon. Thanks!

02/19/05 @ 11:05

Comment from: Vern Rabe [Visitor]

I, too, use that technique often. MS recommends using INFORMATION_SCHEMA views instead of system tables whenever possible. The revised script using INFORMATION_SCHEMA views:

use pubs

DECLARE @c varchar(4000), @t sysname
SET @c = ''
SET @t='authors'

SELECT @c = @c + C.COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE c.TABLE_NAME = @t
ORDER BY ORDINAL_POSITION
SELECT Substring(@c, 1, Datalength(@c) - 2)

Vern

03/12/05 @ 09:45

Comment from: Wayne [Member]

Thanks Vern. Being an "old timer" I forget about the information_schema views.