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
17 comments:
This code rocks!
Thank you - you made my day.
Great!!!!!!!!!!
Thanx..........
Great..........
Thanx
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
Thanks Stephen
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
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
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
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
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
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
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
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
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
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
Many thanks for samsudeenb & stephen for the above scripts. I encountered some problems when I ran them (on SQL Sever 2016, now in April 2018):
1. I have indexes with many columns and includes that end up running out of space in the UDFs (so I changed some variables to NVARCHAR(MAX)
2. I have Unique Constraints that end up becoming unique indexes in both the drop & create scripts (so I changed to ALTER TABLE x ADD/DROP CONSTRAINT ...)
3. I ran into trouble executing the drop & create scripts due to the batch separator GO, so I changed it to ";"
4. For descending columns, "(-)" caused troubles, so I changed to " DESC"
(NB: due to blogger.com's 4096 bytes restriction, I have to break the code into 2 parts)
In the interest of sharing, here are my modified scripts. Kindly advise if there are areas I should improve on...
-- *1. fGetIndexCols
CREATE FUNCTION [dbo].[fGetIndexCols]
(
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @keys NVARCHAR(MAX)
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 + ' DESC'
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + ' DESC'
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 + ' DESC'
END
RETURN (@KEYS)
END
-- *2. fGetIncludedIndex
CREATE FUNCTION [dbo].[fGetIncludedIndex] (
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @V_IncludedCol NVARCHAR(MAX)
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
-- Create/Drop scripts to follow as a separate comment
-- Follow on to earlier comment, here's the drop/add scripts
-- *3. Generate CREATE INDEX/CONSTRAINT statements
SELECT
CASE
WHEN is_unique_constraint = 1 -- For Unique Constraint
THEN 'ALTER TABLE '
+ '[' + a.name + ']' + '.' + '[' + b.name + ']'
+ ' ADD CONSTRAINT '
+ c.name + ' UNIQUE ('
+ DBO.fGetIndexCols (b.name, index_id) + ') '
ELSE 'CREATE ' -- For Non-Clustered Indexes
+ CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE '' END -- Was ELSE ' '
+ '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
END
+ 'ON ['
+ ( SELECT GROUPNAME FROM SYSFILEGROUPS WHERE GROUPID = DATA_SPACE_ID)
+ ']' + ';' -- Was + ']' + char(10) + 'GO'
AS
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 > 98 -- Was 97; however, changing it to 98 did not make a difference to output
AND b.type = 'U'
ORDER BY
c.is_unique_constraint, a.name, b.name -- Was a.name,b.name
;
-- *4. Generate DROP INDEX statements
SELECT
CASE WHEN is_unique_constraint = 1
THEN 'ALTER TABLE ' + '[' + a.name + ']' + '.' + '[' + b.name +']' + ' DROP CONSTRAINT ' + '[' + c.name + ']'
ELSE 'DROP INDEX ' + '[' + a.name + ']' + '.' + '[' + b.name +']' + '.'+ '[' + c.name + ']'
END
+ ';' -- Was + 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 > 98 -- Was 97
AND b.type = 'U'
ORDER BY
c.is_unique_constraint, a.name, b.name -- Was a.name,b.name
;
Post a Comment