A variation on this that is really useful for dynamic SQL generation is getting a column list for a table - I use variations of the code below (variations include excluding identity columns for inserts to an identity table):

SET @Cols = ''

SELECT

@Cols = @Cols + CASE WHEN ORDINAL_POSITION = 1 THEN '' ELSE ',' END + COLUMN_NAME

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_CATALOG=@tableCatalog

AND TABLE_SCHEMA=@tableSchema

AND TABLE_NAME=@tableName