Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

SQL Server does not script indexes by default

Here is a got-ya that happened to me.  In SQL Server I want to generate a script of all the tables in case I need to re-create them.  This is usually the case in a development environment when I’m building a database.  So in Object Explorer I click on the Tables folder, open the tab Object Explorer Details, select all the tables (making sure not to select the System Tables folder), right mouse click and select “Script Table as -> CREATE To -> File”.

Great, I can now drop tables or modify them and can quickly get them back to the way they were because I did the right thing in creating a file with the scripts for them (maybe doing this daily).

So I drop a table, then go to my scripts, find the one for that table and execute it to recreate it.  Uh-oh, where have the indexes gone?  Well, by default, SQL Server does NOT script out the indexes, so they will not be re-created!  To change this, go to Tools -> Options -> SQL Server Object Explorer -> Scripting.  Set “Script indexes” to true.  Also note that “Script triggers” is set to false by default.

So, one of the first things you should do is change the option “Script indexes” to true so you can avoid trying to remember what the indexes were like I had to do.  It was a pain!

Comments

Posted by Anonymous on 29 July 2011

Pingback from  Dew Drop – July 29, 2011 | Alvin Ashcraft's Morning Dew

Posted by Jakub Dvorak on 29 July 2011

Good:) I was always bothering with Tasks -> Generate script whenever I wanted to script completely everything related to object.

Posted by David McKinney on 1 August 2011

@Jakub

Unless I'm mistaken Tasks -> Generate Script also generates based on the options whose defaults are set under Tools / Options (as James discussed).  The key is to review these options to your liking, and then subsequently generated scripts will follow.

But I agree that using Object Explorer Details to generate scripts is a god-send.

Posted by HLogic on 1 August 2011

The latest gotcha I have noted is in SSMS 2005 with SP4 applied (Version 9.00.5000.00), the TEXTIMAGE_ON clause is no longer scripted (even when Script file groups is True) and I don't see an option to include/exclude it.

Posted by cbedford on 1 August 2011

This may be a silly question but I can not select more than one table in SSMS 2008.  Is there a way around this?

Thanks

Char

Posted by HLogic on 1 August 2011

That can only be done on the Object Explorer Details tab.  <F7> to toggle it...

Posted by sharath.chalamgari on 1 August 2011

Need to use the detailed tab in object explorer

Posted by jaybosco on 1 August 2011

Hi Guys,

Try out this another option, Create the query without indexes, Doesnt matter how it is recreated, but in any state if you want to decide on the Index type then u can directly fix in the Clusterd or Non-clustered index, by just using the tuning Wizard, coz you cant anticipate the nature of the data, rather explicitly implying the Index.

Run a script without indexes and recreate Clusterd.. I think this would be a simple way...

Posted by pat.hall on 1 August 2011

DATA_COMPRESSION is also not scripted by default.

Posted by kobusv on 22 July 2013

Thanks James

Leave a Comment

Please register or log in to leave a comment.