Nice script Linson. I had to make a couple tweaks you might want to know about:

1) The script that is generated does not declare varchar(max) columns correctly (it declares them as varchar(-1), which results in a parsing error when the generated script is executed). I resolved this using the following modification:

ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+ case when @CharacterLen < 1 then 'max' else @CharacterLen end +')' ELSE '' END + ','

2) Source data that contains embedded single quotes (e.g., "Murphy's Law") are not handled correctly. I resolved this using the following modification:

THEN 'CASE WHEN @'+@ColumnName+' IS NOT NULL THEN '+'''''''''+'+'replace(@'+@ColumnName + ', char(39), char(39)+char(39))' + '+'''''''''+' ELSE ''NULL'' END'+'+'',''+'

Thanks for posting the script - it'll come in handy here.