Link to home
Start Free TrialLog in
Avatar of Chuck Lowe
Chuck Lowe

asked on

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.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Avatar of Chuck Lowe
Chuck Lowe

ASKER

@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.
Well, if you DROP a table, all related table objects will be dropped, including indexes.
@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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I used the Vitor suggestion. I choose Drop and Create and broke it into 2 scripts.