I tried to use “Generate SQL / Data Script…” the other day and ran into numerous problems with the options.
- Turning off "Generate USE " also turns off “Use transactions”. Conversely, turning on “Use transactions” also turns on "Generate USE ". I can think of no reason for this to be so.
- Constraints aren’t dropped or disabled before the import; I have to add ‘EXEC sp_MSforeachtable @command1=“ALTER TABLE ? NOCHECK CONSTRAINT ALL”’ to the top and ‘EXEC sp_MSforeachtable @command1=“ALTER TABLE ? CHECK CONSTRAINT ALL”’ to the bottom of the file.
- The “Script tables” option is basically useless.
- [dbo] still shows up in the DDL statements even if “Schema qualify” is turned off.
- Tables are created in alphabetical order with no regard for any constraints. Constraints should be created at the very end.
- Indexes are dropped at the beginning of the data import and recreated at the end; this naturally fails when referential integrity is configured.