<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Thom
»
Moving Indexes
15 posts, Page 1 of 2
1
2
»»
Moving Indexes
Rate Topic
Display Mode
Topic Options
Author
Message
Thom_Bolin
Thom_Bolin
Posted Sunday, September 14, 2008 1:01 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:11 PM
Points: 88,
Visits: 258
Comments posted to this topic are about the item
Moving Indexes
Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
Post #569147
Anipaul
Anipaul
Posted Monday, September 15, 2008 1:14 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 1:35 AM
Points: 4,789,
Visits: 1,336
As a new author you have done a very good job. Good article.
Post #569265
cy-dba
cy-dba
Posted Monday, September 15, 2008 7:16 AM
SSC Eights!
Group: General Forum Members
Last Login: Wednesday, August 17, 2011 7:09 AM
Points: 869,
Visits: 963
Great job! I like how you break down each part of the script with brief descriptions.
Post #569412
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, September 15, 2008 7:30 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357,
Visits: 9,539
Awesome job Tom.
I however have a suggestion for all those articles with 15 different code snippets. Could you include a download link where we can get the whole thing with a single copy / paste?
It would save a lot of time and garantee that we don't screw it up in the process.
Post #569422
Thomas LeBlanc
Thomas LeBlanc
Posted Monday, September 15, 2008 8:37 AM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 20, 2013 3:33 PM
Points: 2,706,
Visits: 717
Get job...
I agree with Ninja's_RGR'us about a single file to download.
Thomas
Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #569500
Thom_Bolin
Thom_Bolin
Posted Monday, September 15, 2008 9:19 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:11 PM
Points: 88,
Visits: 258
Files are now available for download at the bottom of the article, sorry for the delay.
Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
Post #569548
Ninja's_RGR'us
Ninja's_RGR'us
Posted Monday, September 15, 2008 9:24 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357,
Visits: 9,539
Thom (9/15/2008)
Files are now available for download at the bottom of the article, sorry for the delay.
NP, that was faster than the usual poster :D.
Post #569553
SanjayAttray
SanjayAttray
Posted Monday, September 15, 2008 1:27 PM
Hall of Fame
Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924,
Visits: 1,554
Great article. I had an index move task in last week of this month and definitely try your scripts on testing env. and let you know results, how it went through.
SQL DBA.
Post #569767
TheMOTU
TheMOTU
Posted Monday, September 15, 2008 11:13 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:40 PM
Points: 8,
Visits: 77
The complete version for SQL2005/2008 is below. It includes schema... plenty of monkeys use multiple schemas..
Enjoy.
SET NOCOUNT ON
DECLARE
@table_schema_name nvarchar(128),
@table_name nvarchar(128),
@index_name nvarchar(128),
@key_ordinal smallint,
@column_name nvarchar(128),
@sort_order varchar(50),
@is_included_column int,
@index_type_desc varchar(50),
@unique_type varchar(50),
@ignore_dup_key varchar(50),
@fill_factor tinyint,
@is_padded varchar(50),
@allow_row_locks varchar(50),
@allow_page_locks varchar(50),
-------------------------------------------
@table_schema_name_ws nvarchar(128),
@table_name_ws nvarchar(128),
@index_name_ws nvarchar(128),
@key_ordinal_ws smallint,
-------------------------------------------
@text1_create varchar(800),
@text1_create_columns varchar(800),
@text1_create_end varchar(50),
@text2_include_start varchar(800),
@text2_include_columns varchar(50),
@text2_include_end varchar(50),
@text4_with varchar(800),
@text4_with_end varchar(50)
SET @text1_create_end = ')'
SET @text2_include_start = 'INCLUDE ('
SET @text2_include_end = ')'
SET @text4_with_end = ')'
DECLARE C01 CURSOR FOR
SELECT
S.name AS table_schema_name,
O.name AS table_name,
I.name AS index_name,
CASE IC.key_ordinal WHEN 0 THEN 999 ELSE IC.key_ordinal END AS key_ordinal,
C.name AS column_name,
CASE IC.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END AS sort_order,
IC.is_included_column,
I.type_desc AS index_type_desc,
CASE I.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END AS unique_type,
CASE I.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END AS ignore_dup_key,
I.fill_factor,
CASE I.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END AS is_padded,
CASE I.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_row_locks,
CASE I.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_page_locks
FROM
sys.indexes I
INNER JOIN
sys.objects O
ON O.object_id = I.object_id AND O.type_desc = 'USER_TABLE'
INNER JOIN
sys.schemas S
ON S.schema_id = O.schema_id
INNER JOIN
sys.data_spaces D
ON D.data_space_id = I.data_space_id AND D.name = 'PRIMARY'
INNER JOIN
sys.index_columns IC
ON IC.object_id = I.object_id AND IC.index_id = I.index_id
INNER JOIN
sys.columns C
ON C.object_id = IC.object_id AND C.column_id = IC.column_id
WHERE
I.type_desc IN ('NONCLUSTERED')
AND I.is_hypothetical = 0
AND I.is_primary_key = 0 -- Exclude primary key constraints
AND I.is_unique_constraint = 0 -- Exclude unique constraints
AND I.is_disabled <> 1 -- Exclude disabled indexes
ORDER BY 1, 2, 3, 4
OPEN C01
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
SELECT @table_schema_name_ws = @table_schema_name
SELECT @table_name_ws = @table_name
SELECT @index_name_ws = @index_name
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
BEGIN
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
BEGIN
SET @text1_create_columns = ''
SET @text2_include_columns = ''
PRINT
'DROP INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '];'
PRINT ' '
SELECT @text1_create =
'CREATE ' +
@unique_type +
' ' + @index_type_desc +
' INDEX [' +
@index_name_ws + '] ON [' +
@table_schema_name_ws + '].[' +
@table_name_ws + '] ('
SELECT @text4_with =
'WITH (PAD_INDEX = ' + @is_padded + ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
'IGNORE_DUP_KEY = ' + @ignore_dup_key + ', ONLINE = OFF, ' +
'FILLFACTOR = ' + CAST(@fill_factor AS VARCHAR(50)) + ', ' +
'ALLOW_ROW_LOCKS = ' + @allow_row_locks + ', ' +
'ALLOW_PAGE_LOCKS = ' + @allow_page_locks
WHILE @@FETCH_STATUS = 0
AND @table_schema_name_ws = @table_schema_name
AND @table_name_ws = @table_name
AND @index_name_ws = @index_name
BEGIN
IF @is_included_column = 1
BEGIN
SELECT @text2_include_columns = @text2_include_columns + @column_name + ','
END
ELSE
BEGIN
SELECT @text1_create_columns = @text1_create_columns + @column_name + ','
END
FETCH C01 INTO
@table_schema_name,
@table_name,
@index_name,
@key_ordinal,
@column_name,
@sort_order,
@is_included_column,
@index_type_desc,
@unique_type,
@ignore_dup_key,
@fill_factor,
@is_padded,
@allow_row_locks,
@allow_page_locks
END
IF @@FETCH_STATUS = 0 SELECT @index_name_ws = @index_name
SELECT @text1_create_columns = ' ' + SUBSTRING(@text1_create_columns, 1, (DATALENGTH(@text1_create_columns) - 1))
PRINT @text1_create
PRINT @text1_create_columns
PRINT @text1_create_end
IF DATALENGTH(@text2_include_columns) > 0
BEGIN
PRINT @text2_include_start
PRINT ' ' + SUBSTRING(@text2_include_columns, 1, (DATALENGTH(@text2_include_columns) - 1))
PRINT @text2_include_end
END
PRINT @text4_with
PRINT @text4_with_end
PRINT 'ON INDEX;'
PRINT '----'
END
IF @@FETCH_STATUS = 0 SELECT @table_name_ws = @table_name
END
IF @@FETCH_STATUS = 0
SELECT @table_schema_name_ws = @table_schema_name
END
CLOSE C01
DEALLOCATE C01
Post #569932
guillermo.pinales
guillermo.pinales
Posted Tuesday, September 16, 2008 8:35 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 PM
Points: 1,
Visits: 47
There's something I noticed while testing the script, but it seems like the scripts seems to be seeing the statistics as indexes. I don't know if this is the correct approach so i decided to add the next line to the first query.
and dbo.sysindexes.name not like '_WA_Sys_%'
Thanks for it by the way. I've been trying to do this for some time but never got the time for it
Post #570291
« Prev Topic
|
Next Topic »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
