SQLServer Management Studion (2012) Drop and Create table scripts

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.
Chuck LoweAsked:
Who is Participating?
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
I'm also happy that you could manage the issue :)
You can now close this question.
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're scripting directly from a Table (right-click on the table name and then select Script Table as) then you don't have the option to generate scripts for the respective indexes.
For that option appear, you'll need to right-click on the Database Name and then choose Tasks / Generate Scripts, Select Specific Object / Table and click Next. Now click on the Advanced button and you'll many options where one of them is to Script Indexes.
Chuck LoweAuthor Commented:
Thanks but that still gives me exactly the same option as right clicking on the Table and scripting it.
I need to have the Indexes dropped along with the table. But it only does that on a Drop & Create. When I pick (generate) Drop it only issues statements for the DROP Table.

It's strange that it doesn't Drop everything on the DROP generation but does Drop everything on a DROP and CREATE generation!

It seems like it's inconsistent. (not your fault).  I'm trying to avoid generating a DROP and Create script and then copy the Drop statements (for Indexes and Tables) to a DROP script and then copy the Create statement (for Table and Indexes) to a Create Script.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, if you DROP a table, all related table objects will be dropped, including indexes.
Chuck LoweAuthor Commented:
I realize that now.
I'm just wondering why a DROP script just has the statement to DROP the table.
But the DROP and CREATE script Drops the Indexes first, then Drops the table. (and Creates the Table and Indexes)

Anyway for our standards I generated the script as you suggested from the Database - Tasks/Generate Scripts, picked my Tables. choose Drop and Create on Advanced. Then copied the Drops into one .sql file and copied the Creates into another .sql file.
Tested it on my Development box.
This will keep our group happy.

Chuck LoweAuthor Commented:
I used the Vitor suggestion. I choose Drop and Create and broke it into 2 scripts.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.