Get a CSV list of columns in a table

Question

Get a CSV list of columns in a table

haveyoumetcp

SSC Eights!

Points: 904
More actions
March 28, 2015 at 11:09 am

#315992

Comments posted to this topic are about the item Get a CSV list of columns in a table

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

brian.miller 15246 Valued Member Points: 55 More actions · Answer 1

Thank you for the post. However it appears to only work for tables in the 'dbo' schema and returns the columns in alphabetical order.

The modified script below gives the user the option to but the schema name and also outputs the columns in the order within the table:

DECLARE @TableName SYSNAME = 'OptionalSchemaName.tableName'

DECLARE @Schema SYSNAME = LEFT(@TableName,NULLIF(CHARINDEX('.',@TableName),0)-1)

SET @TableName = SUBSTRING(@TableName,ISNULL(LEN(@Schema)+2,1),128)

SELECT

REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]

FROM sys.columns

WHERE OBJECT_NAME(object_id) = @TableName

and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')

ORDER BY column_id

FOR XML PATH('') )),1,1,'')),' ','')

GO

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 2

Why all the complication with REPLACE(REVERSE(STUFF(REVERSE ...

I typically do this to get a comma separated list:

SELECT STUFF((SELECT ',' + name AS [data()]

FROM sys.columns

WHERE OBJECT_NAME(object_id) = @TableName

ORDER BY column_id

FOR XML PATH('')), 1, 1, '')

Put in a leading comma in the list instead of a trailing comma, and then remove it after the list is created with the STUFF command.

Tom

Kain_F Valued Member Points: 60 More actions · Answer 3

/*Another way to get the fields in comma separated list and store the returned string in a VARCHAR is:*/

DECLARE @TableName SYSNAME = 'tablename'

DECLARE @ColumnNames VARCHAR(MAX)

SELECT @ColumnNames = COALESCE(@ColumnNames + ',','') + name

FROM sys.columns

WHERE OBJECT_NAME(object_id) = @TableName

ORDER BY column_id

SELECT @ColumnNames

/* I use COALSCE frequently to concatenate multiple values to pass off to something else as a single variable.*/

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

Tom Bakerman (3/30/2015)
Why all the complication with REPLACE(REVERSE(STUFF(REVERSE ...
I typically do this to get a comma separated list:
SELECT STUFF((SELECT ',' + name AS [data()]
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
ORDER BY column_id
FOR XML PATH('')), 1, 1, '')
Put in a leading comma in the list instead of a trailing comma, and then remove it after the list is created with the STUFF command.
Tom

Be careful with XML PATH:

use tempdb;

create table dbo.test_columns (id int, [this & that] varchar(100));

declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

SELECT STUFF((SELECT ',' + name AS [data()] FROM sys.columns

--WHERE OBJECT_NAME(object_id) = @TableName -- assumes dbo schema

WHERE object_id = object_id(@TableName) -- use object_id function including schema

ORDER BY column_id

FOR XML PATH('')), 1, 1, '')

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 5

brian.miller 15246 (3/30/2015)
Thank you for the post. However it appears to only work for tables in the 'dbo' schema and returns the columns in alphabetical order.
The modified script below gives the user the option to but the schema name and also outputs the columns in the order within the table:
DECLARE @TableName SYSNAME = 'OptionalSchemaName.tableName'
DECLARE @Schema SYSNAME = LEFT(@TableName,NULLIF(CHARINDEX('.',@TableName),0)-1)
SET @TableName = SUBSTRING(@TableName,ISNULL(LEN(@Schema)+2,1),128)
SELECT
REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]
FROM sys.columns
WHERE OBJECT_NAME(object_id) = @TableName
and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')
ORDER BY column_id
FOR XML PATH('') )),1,1,'')),' ','')
GO

No need to parse out the schema name...just use the object_id function to resolve your schema-qualified table reference to an object_id. See my earlier comment regarding the pitfalls of using XML PATH. Also see the later comments about not needing to use REVERSE.

use tempdb;

create table dbo.test_columns (id int, [this & that] varchar(100));

declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

SELECT REPLACE(REVERSE(STUFF(REVERSE((SELECT name + ',' AS [data()]

FROM sys.columns

--WHERE OBJECT_NAME(object_id) = @TableName

--and OBJECT_SCHEMA_NAME(object_id) = ISNULL(@Schema,'dbo')

where object_id(@TableName) = object_id -- no need to parse schema, use object_id()

ORDER BY column_id

FOR XML PATH('') )),1,1,'')),' ','')

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Personally I like the "concatenated aggregate" method sqlservercentral 46394 showed however Erland pointed out some problems with it if you mess with the ORDER BY in specific ways, e.g. adding expressions or referencing columns that are not in the SELECT-column list. Therefore, use with caution.

Here is the XML method that makes use of TYPE:

use tempdb;

create table dbo.test_columns (id int, [this & that] varchar(100));

declare @TableName nvarchar(257); set @TableName = N'dbo.test_columns';

SELECT STUFF(

(SELECT N',' + n

FROM (SELECT

name AS n

FROM sys.columns s2

WHERE object_id = object_id(@TableName)

) r

FOR XML PATH(''),

TYPE

).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS column_list;

The query above results in the proper column names when the column names contain XML escapable characters:

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

August 30, 2016 at 6:27 am

#1898281

That's pretty cool, thanks.