Thursday, 9 July 2009

Generate Index scripts using included columns

This article is a continuation of the previous article http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html with small enhancement to generate the index scripts with included columns. The below function “fGetIncludedIndex is used to get the included columns for a given index.

CREATE FUNCTION fGetIncludedIndex (
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @V_IncludedCol NVARCHAR(200)
select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') +
( select name from sys.syscolumns where id =a.Object_id and colid =a.column_id)
from sys.index_columns a
where object_name(a.object_id) =@objname
and a.index_id =@indid
and a.Is_included_column =1
order by a.index_column_id
return @V_IncludedCol
END


After creating the function in the DB make a small change to the existing query( highlighted in bold) to generate the index scripts with included columns

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) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id)+')' end +
' 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

22 comments:

Nikolaj Henrichsen said...

This code rocks!
Thank you - you made my day.

Randhir said...

Great!!!!!!!!!!
Thanx..........

Randhir said...

Great..........

Thanx

Stephen said...

Thanks for this! Improved it a bit (I think) by giving it the capability of determining schema.
SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
c.name + ' ' +' ON ' + '[' + a.name + ']' + '.' + '[' + b.name + ']' +
'('+
DBO.fGetIndexCols (b.name, index_id) + ')'
+Case when dbo.fGetIncludedIndex(b.name, index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(b.name, index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' + char(10) + 'GO' IndexScript
FROM
sys.schemas a JOIN sys.objects b ON
a.schema_id = b.schema_id JOIN sys.indexes c ON
(b.object_id = c.object_id)
WHERE
c.NAME IS NOT NULL
AND c.Is_Primary_Key =0
AND c.type_desc ='NONCLUSTERED'
AND c.object_id > 97
AND b.type = 'U'
ORDER BY
a.name,b.name

--Then Generate statement to DROP INDEXES

SELECT
'DROP INDEX ' + '[' + a.name + ']' + '.' + '[' + b.name +']' + '.'+ '[' + c.name + ']' + CHAR(10) + 'GO'
FROM
sys.schemas a JOIN sys.objects b ON
a.schema_id = b.schema_id JOIN sys.indexes c ON
(b.object_id = c.object_id)
WHERE
c.NAME IS NOT NULL
AND c.Is_Primary_Key =0
AND c.type_desc ='NONCLUSTERED'
AND c.object_id > 97
AND b.type = 'U'
ORDER BY
a.name,b.name

Samsudeen B said...

Thanks Stephen

Mike said...

Great Code. I have also stuck in such kind of code. This is one of the most common error in SQL Server Database. I read many blog posts, forums & articles about this error. In many cases, the solution that you provided is not surely resolve this error.
sap implementation guide

Anonymous said...

Hey would you mind sharing which blog platform you're using? I'm going to start my own blog soon but I'm having a tough time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I'm looking for something
unique. P.S Apologies for getting off-topic but I had to
ask!

Also visit my web page :: chestfatburner.com

Anonymous said...

Neat blog! Is your theme custom made or did you download it from somewhere?

A theme like yours with a few simple adjustements
would really make my blog jump out. Please let me know where you got
your theme. Thanks

Feel free to visit my website ... chestfatburner.com

Anonymous said...

Hi! I'm at work surfing around your blog from my new iphone! Just wanted to say I love reading through your blog and look forward to all your posts! Carry on the fantastic work!

my web page ... Causes of Saggy Chest

Anonymous said...

I visited multiple web pages but the audio quality for audio songs
current at this site is actually superb.

My web page: Considering gynecomastiasurgery - Permit not the costs deter an individual

Anonymous said...

I think the admin of this website is really working hard in support of his web
site, since here every stuff is quality based material.



Feel free to surf to my blog post chestfatburner.com

Anonymous said...

I'm impressed, I must say. Rarely do I encounter a blog that's equally
educative and engaging, and without a doubt, you have hit the nail on the head.
The issue is something which not enough people are speaking intelligently about.
I am very happy I found this in my hunt for something concerning
this.

Stop by my web site - How to Unfastened Fat Through Below your Armpits

Anonymous said...

When you are looking for a quiet and fun component of your paphos car hire in Wonderful, France well in advance with gaps of time
to do something about it.

My web page; lietuvosspauda.Lt

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

oakleyses said...

north face, sac michael kors, abercrombie and fitch, scarpe hogan, nike air max, hollister, louis vuitton, nike free, true religion outlet, ray ban uk, vans pas cher, oakley pas cher, ray ban pas cher, lululemon, hollister, air max pas cher, michael kors canada, north face pas cher, air jordan, longchamp pas cher, nike trainers, nike free pas cher, hermes pas cher, air max, sac louis vuitton, true religion outlet, guess pas cher, converse pas cher, true religion, mulberry, nike huarache, timberland, ralph lauren pas cher, burberry pas cher, nike air force, sac vanessa bruno, lacoste pas cher, louis vuitton uk, nike roshe run, chaussure louboutin, longchamp, ralph lauren, barbour, nike roshe, nike blazer pas cher, michael kors uk, tn pas cher, new balance pas cher, louis vuitton pas cher, nike air max

oakleyses said...

giuseppe zanotti, insanity workout, canada goose, valentino shoes, bottega veneta, new balance outlet, canada goose pas cher, marc jacobs outlet, moncler, asics shoes, beats headphones, hollister, reebok shoes, ugg boots, canada goose outlet, vans outlet, rolex watches, ugg outlet, moncler outlet, canada goose outlet, mac cosmetics, lululemon outlet, uggs outlet, wedding dresses, ferragamo shoes, soccer shoes, babyliss pro, north face outlet, mont blanc pens, jimmy choo shoes, mcm handbags, longchamp, ugg, canada goose uk, soccer jerseys, ghd, birkin bag, uggs on sale, celine handbags, moncler, nfl jerseys, roshe run, chi flat iron, instyler ionic styler, herve leger, p90x workout, ugg soldes, abercrombie and fitch, north face jackets, canada goose outlet

شركة الطيب said...


شركة تنظيف بالدمام
شركة تنظيف كنب بالدمام
شركة تنظيف مجالس بالدمام
شركة تنظيف شقق بالدمام
شركة تنظيف بيوت بالدمام
شركة تنظيف منازل بالدمام
شركة تنظيف فلل بالدمام
شركة تنظيف عمائر بالدمام
شركة تنظيف خزانات بالدمام
شركة تنظيف مسابح بالدمام
شركة تنظيف اواني بالدمام
شركة تنظيف كشف تسربات بالدمام
شركة نقل اثاث بالدمام
شركة نقل اثاث بالدمام رخيصة
شركة نقل عفش بالدمام
شركة تخزين اثاث بالدمام

شركة الطيب said...


شركة تنظيف بجدة
شركة تنظيف واجهات زجاج بجدة
شركة تنظيف واجهات حجر بجدة
شركة رش مبيدات بجدة
شركة نقل اثاث بجدة
شركة تنظيف منازل بجدة
شركة تنظيف خزانات بجدة
شركة تنظيف شقق بجدة
شركة كشف تسربات بجدة
شركة مكافحة حشرات بجدة
شركة مكافحة صراصير بجدة
شركة مكافحة فئران بجدة
شركة رش مبيدات حشرية بجدة



ninest123 said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

ninest123 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

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 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