Btw this procedure doesn't work for partitioned tables. For some reason, it locks the table and does a table scan anyway.
The workaround we came up with was to add check constraints for instant partition attachment, lock the table, detach all the partions, update the type on each partition, update the parent table type, then reattach all the partitions.
In our case, we didn't already have those instant-attach check conditions, so we incurred table scans anyway to validate those, but if you were to make multiple updates, you only have to do that once.
The workaround we came up with was to add check constraints for instant partition attachment, lock the table, detach all the partions, update the type on each partition, update the parent table type, then reattach all the partitions.
In our case, we didn't already have those instant-attach check conditions, so we incurred table scans anyway to validate those, but if you were to make multiple updates, you only have to do that once.