How do you create and update your SQL scripts?

If automating something saves time and human error why wouldn't you do it? I would try auto-generating SQL if:

1. I had already populated a test database and built up the schema in an organic fashion by writing and executing ad hoc SQL non-queries which I hadn't kept track of; and
2. If the database was reasonably simple and with a low amount of normalisation

Another rare case might be if you had a massive list of data sets for which the schema needed to be extracted in the form of SQL.

The benefits of writing by hand are that you are aware of every part of the design and better able to enforce data integrity (for instance through use of foreign keys or limiting data types). In short, I would agree with your list of pros.

For upgrade scripts I strongly prefer writing these by hand because they're usually fairly short and it's nice for them to be more readable for the people who end up running them.

One tip I'd have for upgrade scripts is to make sure each script adds a row stating the upgrade version number in a table which is exclusively there to ensure the database is versioned correctly. Something like:

INSERT INTO DB_VERSION (upgrade_time, version_from, version_to, comment)    VALUES ('2011-07-04T120320', '2.2.4', '2.3', 'add column x to table y.')