July 8, 2010 at 4:21 am
Hi,
in Management Studio you can do for a single index or key the following SCRIPT INDEX AS... DROP and CREATE TO a new query editor window or file.
Is there a means to create a DROP and CREATE TO script for all the indexes and/or keys in a table.
I have to change collation in a database on more than 10000 columns and a lot of them are used in keys or indexes.
Can anyone help?
Best regards,
July 8, 2010 at 8:09 am
my memory is not as good as it used to be, but i don't think you can do it using dynamic SQL. i think i tried this one time a year or so ago on one table and it wouldn't allow me. i had to script out each index's drop and create statements.
July 8, 2010 at 8:25 am
I fond a very good article on the web:
SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script[/url]
from Brian Cidern
After adapting the scripts and follwing the instructions, it did a good job for me
One remark, you have to run sp_configure to enable 'Ole Automation Procedures' during generation of the CREATE script.
July 8, 2010 at 5:37 pm
alen teplitsky (7/8/2010)
my memory is not as good as it used to be, but i don't think you can do it using dynamic SQL. i think i tried this one time a year or so ago on one table and it wouldn't allow me. i had to script out each index's drop and create statements.
I have a similar memory, but it might be because it's not worth creating a script to run the commands one-time when you can just get the commands scripted.
Another option you could try would be to disable the indexes and then rebuild. It's a lot easier to script "ALTER INDEX <indexname> ON <schema>.<table> DISABLE" and then "ALTER INDEX <indexname> ON <schema>.<table> REBUILD", instead of worrying about the actual definition of the index (included columns, etc)
July 13, 2010 at 5:49 pm
Jim, excellent thought. I had created a function to script out the create and drop indexes for a table, and I should have just used that idea instead...would make it also easier to script cross database alter index calls.
July 13, 2010 at 10:55 pm
Try to use a db comparison tool like SQL Delta to compare and take script
i too had same problem
July 13, 2010 at 11:14 pm
Check out this thread:
http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx
There is a script there as well as a link to another thread that has multiple revisions for a script that will do what you are looking to accomplish.
A slight modification could likely also generate the drop statements for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 15, 2010 at 1:21 pm
I don't necessarily have a script to provide, but depending on how many tables there are, you could, in SSSM, highlight the Indexes node under a table, pull up the Object Explorer Details, and then right click on the multiple-selected indexes and script them as drop and create to new window/file/agent job.
May not necessarily help in this situation, but it's something else to add to the discussion.
Regards,
Steve
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy