Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Drop index, alter column, Create Index

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)
1 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.
saturationAuthor Commented:
Is there a system stored procedure that will generate either of those from query analyzer and/or MGMT Studio?
Anthony PerkinsCommented:
No.  But as indicated you can use SSMS to create the appropriate scripts.
Alpesh PatelAssistant ConsultantCommented:
You can see the date time in SSMS (Created date for Index)

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now