Drop index, alter column, Create Index

Posted on 2011-04-20
Last Modified: 2012-05-11
I need to drop indexes to alter some fields.   However, I need to rebuild those indexes just as they were before they were dropped.   Some of my indexes have more than one field on them.   How do I ensure the indexes are re-created the same way they were before dropped?

DROP INDEX table_name.index_name
ALTER table_name ALTER COLUMN field_name nvarchar (x)
Question by:saturation
    LVL 1

    Accepted Solution

    Simplest way is to have SQL Server Management Studio generate Create scripts for you.  In the SSMS Object Browser, expand your Table Object, then expand the Indexes container.  Select the indexes you want to recreate, right click and click Script As -> Create To and select your destination.  In your case, I would use Clipboard so you could just paste the create back in to your existing script after your alter statements.

    Author Comment

    Is there a system stored procedure that will generate either of those from query analyzer and/or MGMT Studio?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    No.  But as indicated you can use SSMS to create the appropriate scripts.
    LVL 21

    Expert Comment

    by:Alpesh Patel
    You can see the date time in SSMS (Created date for Index)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now