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 UsageTo use the function on a table called Person do the following.
select dbo.GetColumnList('Person', 1, ', ')
sample results: [FirstName], [LastName], [Phone]
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))
Declare @ColumnList as nvarchar(MAX)
Declare @BeginningBracket as nvarchar(1)
Declare @EndingBracket as nvarchar(1)
if @IncludeBrackets = 1
SET @BeginningBracket = '['
SET @EndingBracket = ']'
SET @BeginningBracket = ''
SET @EndingBracket = ''
@ColumnList = ISNULL(@ColumnList + @Delimiter, '')
+ @BeginningBracket + COLUMN_NAME + @EndingBracket
where TABLE_NAME = @TableName
ORDER BY COLUMN_NAME
Advances UsageIf 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.
dbo.GetColumnList(TABLE_NAME, 1, ', ')