Using SSMS 2012 (on a 2008R2 database). When I do Script Table as and choose Drop and Create to a file, it will generate a script that Drops all indexes, Drops the table and then Creates the table and creates the Indexes.
But when I choose to Script Table as Drop to file, it only creates a script to Drop the table. It doesn't generate any Drop Index statements.
I checked under Tool/Options/SQL Server Object Explorer/Script and I don't see that I have anything else to setup.
When I choose Script Table as Create to file it generates a Script that creates the table and creates the indexes.
My issue is I've been told (for our DBA team) to separate the Drop tables scripts from the Create table scripts. This is because (and Yes this is true) If it's a new table and I send one scrip that Drops and Creates, if it issues the error the object isn't present (because it's a new table so it can't be dropped) but continues to create the table and indexes, The DBA team sees this as an error. (Please don't ask me to explain why DBA's can't recognize this as ok).
I don't want to have to take the Drop and Create script and cut and paste into separate files for Dropping Indexes and Tables and Creating Table and Indexes.
Or does Dropping a table drop the indexes? It seems it does. When I run the Drop the table by itself (do not have drop index code) and then run the Create Table it does not give me an error that indexes exist. If this is so why does the Drop and Create generated statements to drop the indexes before dropping the table? But the generated Drop statement only contains a Drop statement for the table?
I wanted to change the options to Check for Existence of the object when creating the Drop and Create script but that is not acceptable to the team.