Get Comma Separated List of Columns for a Table using T-SQL

Using T-SQL (Microsoft SQL Server) you can get a list of columns (delimited by commas or other delimiter of choice) for a given table using one of the function below. There are two ways to call it. Either one words, but it is up to your personal preferences and also how safe the column names are as to which function you use.

If ever in doubt, pass a 1 for @IncludeBrackets is the safest because it puts all the column names in square brackets. This allows column names to have spaces and other special characters that would not normally be allowed. This often happens when importing data from Excel and using the default names for the columns. This is because it uses the column headings in Excel which typically have spaces in them because they are meant to be human readable.

With that said, if I created the columns I always use Just alphanumeric characters and no spaces, etc so my column names are known to be safe. In this scenario, I personally feel it is easier to read the column names without the brackets so in this case I pass 0 for the @IncludeBracket parameter.

The first parameter is simply the table name.

Basic Usage

To use the function on a table called Person do the following.

select dbo.GetColumnList('Person', 1, ', ')
sample results: [FirstName], [LastName], [Phone]
or
select dbo.GetColumnList('Person', 0, ', ')

sample results: FirstName, LastName, Phone

Function Definition (Code)

Here is the code to create the SQL function



create function GetColumnList(@TableName as nvarchar(255), @IncludeBrackets as bit, @Delimiter as nvarchar(500))
returns nvarchar(max)
as
BEGIN
Declare @ColumnList as nvarchar(MAX)
Declare @BeginningBracket as nvarchar(1)
Declare @EndingBracket as nvarchar(1)

if @IncludeBrackets = 1
BEGIN
SET @BeginningBracket = '['
SET @EndingBracket = ']'
END
else
BEGIN
SET @BeginningBracket = ''
SET @EndingBracket = ''
END

select
@ColumnList = ISNULL(@ColumnList + @Delimiter, '')
+ @BeginningBracket + COLUMN_NAME + @EndingBracket
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName
ORDER BY COLUMN_NAME

return @ColumnList
END

Advances Usage

If you want to do the same thing, but for all tables in your database you can do something like the following. You can of course add a where clause, etc to select just the sames you want as well.


select 
dbo.GetColumnList(TABLE_NAME, 1, ', ')
from INFORMATION_SCHEMA.TABLES