Wednesday, 14 November 2007

Scripts to Drop and ReCreate Indexes in SQL Server 2005

SQL Server 2005 “GENERATE SCRIPTS” wizard does not have the option to generate the Index scripts alone from the database. The below script can be used to DROP and recreate the Indexes from the database.

Steps to be followed

1. Create a function to retrieve the list of key columns used in the Index definition (this function is useful for retrieving the multiple index columns in case of composite Index)

CREATE FUNCTION fGetIndexCols
(
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(131)
AS
BEGIN

DECLARE @keys NVARCHAR(2126)
DECLARE @i int, @thiskey NVARCHAR(131) , @objid BIGINT

SELECT @objid = object_id(@objname)
SELECT @keys = index_col(@objname, @indid, 1), @i = 2

IF (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
SELECT @keys = @keys + '(-)'
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'
WHILE (@thiskey is not null )
BEGIN

SELECT @keys = @keys + ', ' + @thiskey, @i = @i + 1
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'

END
RETURN (@KEYS)

END

2. Execute the below script to generate the CREATE INDEX statements from the database. I have written this script to generate the scripts for NON CLUSTERED indexes for clustered indexes change the where clause type_desc = ‘CLUSTERED’

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) +
')'+
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

3. Execute the below script to generate the DROP INDEX statements

SELECT ' DROP INDEX' + OBJECT_NAME (OBJECT_ID) +'.'+ NAME
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

I have continued this article with a modified query to generate index using included columns http://samsudeenb.blogspot.com/2009/07/this-article-is-continuation-of_09.html


248 comments:

«Oldest   ‹Older   201 – 248 of 248
Anonymous said...

hello!,I really like your writing very a lot! percentage we keep in touch
more about your article on AOL? I require a specialist on this space
to unravel my problem. Maybe that's you! Taking a look ahead to peer you.

Here is my web-site :: make money online easy

Anonymous said...

Howdy! Would you mind if I share your blog with my myspace group?
There's a lot of folks that I think would really enjoy your content. Please let me know. Many thanks

my website How To Make Money Fast At Home

Anonymous said...

Great weblog here! Additionally your website quite a bit up very fast!
What host are you using? Can I get your affiliate
hyperlink to your host? I want my web site loaded up as fast as yours
lol

Also visit my web page - free stock trading software download

Anonymous said...

Appreciate this post. Let me try it out.

Also visit my webpage :: best stock trading software

Anonymous said...

Undeniably believe that which you stated. Your favorite justification appeared to be
on the net the simplest thing to be aware of. I say
to you, I certainly get annoyed while people think about worries that
they plainly don't know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side effect , people could take a signal. Will likely be back to get more. Thanks

Look at my web site: legitimate work from home jobs

Anonymous said...

Hi there! I could have sworn I've been to this website before but after going through some of the articles I realized it's
new to me. Nonetheless, I'm certainly delighted I found it and I'll be bookmarking it and checking back often!


Feel free to visit my web page ... making fast money online

Anonymous said...

Greetings, I believe your blog could possibly be having browser compatibility problems.
When I look at your blog in Safari, it looks fine however,
when opening in I.E., it's got some overlapping issues. I simply wanted to give you a quick heads up! Apart from that, wonderful blog!

Check out my page :: work at home jobs

Anonymous said...

It's actually a cool and helpful piece of info. I'm satisfied that you simply shared this useful
information with us. Please stay us informed like this. Thank you for
sharing.

Look into my weblog; how to search For A job

Anonymous said...

I used to be suggested this web site by means of my cousin.

I am no longer positive whether this post is written by way of him as
no one else realize such distinctive about my problem.
You're incredible! Thank you!

My blog post - online typing jobs without investment

Anonymous said...

What a material of un-ambiguity and preserveness of precious experience regarding
unpredicted feelings.

Feel free to visit my web-site; highest paying part time jobs

Anonymous said...

My brother recommended I would possibly like this
blog. He was once entirely right. This publish
actually made my day. You can not believe just how much time I had spent for this
information! Thank you!

Here is my page - what are the highest paying jobs in australia

Anonymous said...

Exceptional post however , I was wondering if you could write a litte
more on this subject? I'd be very grateful if you could elaborate a little bit further. Appreciate it!

Check out my site best forex system trading

Anonymous said...

Hi there! Someone in my Facebook group shared this website
with us so I came to take a look. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers!
Wonderful blog and excellent style and design.

Have a look at my web site stock market trading hours

Anonymous said...

Greetings! I know this is somewhat off topic but I was wondering which blog platform are you using for this website?
I'm getting tired of Wordpress because I've had issues with hackers and I'm looking at options for another platform. I would be awesome if you could point me in the direction of a good platform.

Feel free to surf to my web page: how to earn extra money online

Anonymous said...

Hello there, You have done a fantastic job. I will certainly digg it and personally suggest to my friends.
I am sure they will be benefited from this website.

Feel free to visit my web page - how to make a lot of money fast

Anonymous said...

Link exchange is nothing else but it is simply placing the other person's weblog link on your page at proper place and other person will also do similar for you.

My website: How To Earn Online Money

Anonymous said...

If some one wishes to be updated with most up-to-date technologies after that he must
be pay a quick visit this web site and be up to date all the time.


Also visit my web page ... Day Trading stock

Anonymous said...

For latest information you have to go to see world wide web and on internet I found this web
site as a most excellent website for most recent updates.


Also visit my web-site organic coffee

Anonymous said...

Wow, wonderful blog layout! How lengthy have you ever been running a blog
for? you made blogging look easy. The total glance of your web site is great, let alone
the content material!

Feel free to visit my web blog; how to make money working from Home

Anonymous said...

Good day! Would you mind if I share your blog with my twitter group?
There's a lot of people that I think would really enjoy your content. Please let me know. Thanks

Feel free to surf to my webpage online work from home jobs

Anonymous said...

Magnificent beat ! I wish to apprentice while you amend your site, how could i subscribe for a blog website?
The account aided me a acceptable deal. I had been
a little bit acquainted of this your broadcast offered bright clear concept

Feel free to surf to my web page; find jobs in north carolina

Anonymous said...

This website was... how do I say it? Relevant!! Finally I
have found something that helped me. Thanks!

Also visit my page ... to make money from home

Anonymous said...

I really like your blog.. very nice colors & theme.
Did you design this website yourself or did you hire someone
to do it for you? Plz reply as I'm looking to design my own blog and would like to know where u got this from. thanks a lot

my blog :: work at home companies

Anonymous said...

Helpful info. Fortunate me I discovered your web site
by chance, and I am shocked why this twist of
fate didn't came about earlier! I bookmarked it.

Also visit my web blog ... Work from home jobs in utah

Anonymous said...

My brother suggested I might like this blog. He was entirely right.
This post actually made my day. You can not consider just how so
much time I had spent for this info! Thank you!

Here is my homepage how to make money working from home

Anonymous said...

I all the time used to read paragraph in news papers but now
as I am a user of net thus from now I am using net for
articles or reviews, thanks to web.

My web blog ... cedar finance company

Anonymous said...

Thanks for your personal marvelous posting! I actually enjoyed reading it,
you might be a great author. I will make sure to bookmark your blog and will often come back from now on.
I want to encourage that you continue your great work,
have a nice afternoon!

Here is my weblog :: how to earn money online for free

Anonymous said...

We're a group of volunteers and starting a new scheme in our community. Your site offered us with valuable information to work on. You have done an impressive job and our whole community will be grateful to you.

Also visit my web-site: http://www.youtube.com/watch?v=FZOuC8FraM0

Anonymous said...

We stumbled over here by a different page and thought I may as well check things out.
I like what I see so now i'm following you. Look forward to looking into your web page again.

Stop by my web page: http://www.youtube.com/watch?v=FZOuC8FraM0

Anonymous said...

Hola! I've been following your website for some time now and finally got the bravery to go ahead and give you a shout out from Porter Tx! Just wanted to mention keep up the great job!

Check out my blog post cedarfinance

Anonymous said...

Please let me know if you're looking for a author for your weblog. You have some really good articles and I feel I would be a good asset. If you ever want to take some of the load off, I'd
really like to write some material for your blog in exchange for
a link back to mine. Please shoot me an e-mail if interested.
Thank you!

my web page: how to make money with clickbank

Anonymous said...

Just want to say your article is as astounding.

The clearness on your submit is just nice and i could
assume you are a professional in this subject. Fine together with your permission allow me
to snatch your RSS feed to keep up to date with forthcoming post.
Thanks a million and please continue the gratifying work.


Also visit my blog: http://www.youtube.com/watch?v=yWtQw9OzNwY

Anonymous said...

Have you ever considered publishing an e-book or guest authoring on other blogs?

I have a blog based upon on the same information you discuss and would really
like to have you share some stories/information. I know my viewers would appreciate your work.
If you're even remotely interested, feel free to shoot me an e-mail.

Review my website http://www.youtube.com/watch?v=yWtQw9OzNwY

Anonymous said...

My brother recommended I might like this website. He was
totally right. This post actually made my day. You cann't imagine simply how much time I had spent for this info! Thanks!

my web blog - earn money through internet

Anonymous said...

Nice blog! Is your theme custom made or did you download it from somewhere?
A design like yours with a few simple adjustements would really make my blog stand
out. Please let me know where you got your design.

Thank you

Take a look at my weblog - earn money online fast and free

Anonymous said...

Thank you for the auspicious writeup. It if truth be told was once a entertainment
account it. Look complex to far delivered agreeable from you!
However, how can we keep up a correspondence?

my web blog ... forex trading system

Anonymous said...

You've made some decent points there. I checked on the internet to find out more about the issue and found most people will go along with your views on this website.

Take a look at my web blog :: how to get money for bills

Anonymous said...

Thanks for every other great post. The place else may
just anyone get that type of information in such an ideal method of writing?

I've a presentation next week, and I'm at the look for such info.


My weblog :: continue reading this

Anonymous said...

It's going to be finish of mine day, but before finish I am reading this wonderful post to increase my knowledge.

Feel free to surf to my blog post - http://twitter.com/troublepscologe

Anonymous said...

Thanks for finally talking about > "Scripts to Drop and ReCreate Indexes in SQL Server 2005" < Liked it!

My web site ... http://www.

youtube.com/watch?v=pwex99npRdc

Anonymous said...

Hi there! This post could not be written any better!
Reading through this post reminds me of my previous room mate!
He always kept talking about this. I will forward this article to him.
Fairly certain he will have a good read. Thank you for sharing!


My weblog binary options trading

Anonymous said...

Hi there I am so glad I found your webpage, I really found you
by mistake, while I was searching on Bing for something else, Regardless I am
here now and would just like to say thank you for a tremendous post and a all round exciting blog (I
also love the theme/design), I don’t have time to read through it all at the moment but I have book-marked it and
also included your RSS feeds, so when I have time I will be back to read a lot more,
Please do keep up the great work.

Have a look at my web blog: http://www.youtube.com/watch?v=Al2XPf2DgwE

Anonymous said...

Oh my goodness! Amazing article dude! Many thanks,
However I am going through problems with your RSS.
I don't understand why I can't join it. Is there anybody else getting the same RSS problems?
Anyone who knows the solution can you kindly respond?

Thanx!!

Check out my weblog: wolpy.com

Anonymous said...

When I initially commented I clicked the "Notify me when new comments are added" checkbox and now each
time a comment is added I get several emails with the same
comment. Is there any way you can remove me from that service?
Thanks a lot!

Also visit my page; make money online work from home

Anonymous said...

Greetings! Very helpful advice within this post! It's the little changes that will make the biggest changes. Thanks a lot for sharing!

Here is my site ... Cedarfinance

SDC said...

Woah dude! SPAM CITY!

Anonymous said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Anonymous said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

«Oldest ‹Older   201 – 248 of 248   Newer› Newest»