Thanks for all the positive feedback so far, in particular raulggonzalez for covering off the NULL issue I'd overlooked.
For the less confident raulggonzalez's line needs to be inserted into the script after the first END and before "+ ';'" with an extra " + " before it.
Or you can change:
+ ';' + CHAR(13) + CHAR(10)
+ (CASE WHEN c.IS_NULLABLE = 'NO' THEN ' NOT' ELSE '' END) + ' NULL;' + CHAR(13) + CHAR(10)
RichB - I don't think this is something you'd want to run in a big production database without thorough testing - the initial conversion wouldn't be the problem but the unfiltered UPDATE statement could certainly wreak havoc - this was a dev scenario.
mike brockington - as mentioned there should be a performance gain in the common case where most of the rows actually contain 8k or less data.