How can I automate the scripting of a database/objects in SQL Server?

A. You can do this using the SQL-DMO objects that SQL supports. These can be called via the SQL OLE interface. There is a help file for the SQL DMO objects included with SQL Server and there is an icon for it in your SQL group.

For an example of using the SQL OLE interface see Q152801 in the MS Kb. Alternatively you can code your own VB app to call the DMO routines yourself.

For SQL 7.0 you could also use the SCPTXFT.EXE utility found in the UPGRADE directory to script objects. You can schedule this using SQLAgent or run from a SP etc. Do a SCPTXFT /? to see the parameters - which are self explanatory. (If it isn't on your hard-drive then look in the upgrade directory on the CD).

See Q220163 for more details.