Also, a slightly more robust way to get the column names and and data types:
declare @collist nvarchar(max),
@schema nvarchar(128),
@table nvarchar(128);
set @schema = N'dbo';
set @table = N'Test';
select @collist = STUFF((select N', ' + col.name + N' ' + typ.name +
case when typ.name in (N'nchar',N'char',N'binary') then '(' + cast(col.max_length as varchar) + ')'
when typ.name in (N'nvarchar',N'varchar',N'varbinary') then
case when col.max_length = -1 then N'(max)'
else case when typ.name in (N'varchar',N'varbinary') then N'(' + cast(col.max_length as nvarchar) + N')'
else N'(' + cast(col.max_length/2 as nvarchar) + N')'
end
end
when typ.name in (N'numeric',N'decimal') then N'(' + cast(typ.precision as nvarchar) + N',' + cast(typ.scale as nvarchar) + N')'
when typ.name in (N'float',N'real') then N'(' + cast(typ.precision as nvarchar) + N')'
when typ.name in (N'datetime2',N'datetimeoffset',N'time') then '(' + cast(typ.scale as nvarchar) + N')'
else ''
end
from
sys.schemas sch
inner join sys.tables tab
on (sch.schema_id = tab.schema_id)
inner join sys.columns col
on (tab.object_id = col.object_id)
inner join sys.types typ
on (col.system_type_id = typ.system_type_id and col.user_type_id = typ.user_type_id)
where
sch.name = @schema and
tab.name = @table
order by
col.column_id
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');
select @collist;
Ignore this code, replace issue modifying the code;