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