@a.guillaume
I'm trying to shy away from creating a View or Table for that matter specifying the columns, because if there are any changes to a Table I will need to change a View as well.
hence:
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';
SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END
FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity= 0
ORDER BY column_id;
this code is the same as creating a #temp table, any changes to my tables won't impact the procedure.