Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Indexes Expand / Collapse
Author
Message
Posted Sunday, September 14, 2008 1:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:17 PM
Points: 88, Visits: 285
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
Posted Monday, September 15, 2008 1:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,181, Visits: 1,368
As a new author you have done a very good job. Good article.


Post #569265
Posted Monday, September 15, 2008 7:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Monday, September 15, 2008 7:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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
Posted Monday, September 15, 2008 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:01 AM
Points: 2,978, Visits: 762
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
Posted Monday, September 15, 2008 9:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 3:17 PM
Points: 88, Visits: 285
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
Posted Monday, September 15, 2008 9:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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
Posted Monday, September 15, 2008 1:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:29 PM
Points: 3,924, Visits: 1,586
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
Posted Monday, September 15, 2008 11:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 16, 2008 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 10:36 AM
Points: 1, Visits: 60
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 »

Add to briefcase 12»»

Permissions Expand / Collapse