自動產生資料表欄位的SQL
select
'alter table [' + SCHEMA_NAME(schema_id) + '].[' + o.name +']
add [' + c.name + '] ' + TYPE_NAME(system_type_id) +
CASE WHEN system_type_id in (165,173,167,175,231,239) THEN
CASE WHEN MAX_LENGTH > 8000 OR MAX_LENGTH = -1 THEN '(max)'
ELSE isnull('('+ CASE WHEN system_type_id in (231,239) THEN
RTRIM(MAX_LENGTH/2) ELSE RTRIM(MAX_LENGTH) END +')', '')
END ELSE ''
END
from sys.columns c join sys.objects o
on c.object_id=o.object_id where o.type='U'