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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
Chuck LoweAuthor Commented:
@Vitor
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, if you DROP a table, all related table objects will be dropped, including indexes.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Chuck LoweAuthor Commented:
@Vitor
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.

Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm also happy that you could manage the issue :)
You can now close this question.
Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck LoweAuthor Commented:
I used the Vitor suggestion. I choose Drop and Create and broke it into 2 scripts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.