Solved

Resize varchar column which is used in one or more indices

Posted on 2015-01-27
4
67 Views
Last Modified: 2015-02-11
I've got a client whose database I am attempting to update and modify some SQL Server tables.

One of the fields which exists in about a dozen SQL Server tables, and which is included in one or more clustered or unclustered indices in each of these tables needs to be resized from varchar(6) to nvarchar(10).  But when I tried to simply alter the columns data type and width, I got an error indicating that an index is dependent on the column.

Is there an easy way to drop the index, modify the column structure, then recreate the index?  If I could do this as a SP and simply pass in the name of the table, that would be even better.
0
Comment
Question by:Dale Fye (Access MVP)
4 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40574467
I am pretty sure that if you use the Table Designer is SSMS it will do it all for you. Just a note
0
 
LVL 14

Accepted Solution

by:
nishant joshi earned 350 total points
ID: 40574849
Hi Dale,

You can use below code to drop and create index which has defined column.

Thanks,

DECLARE @ColumnName VARCHAR(255) = 'ProductId'

DECLARE @dropsql VARCHAR(MAX)

SELECT @dropsql = ''


;WITH t1
AS
(
SELECT ' DROP  INDEX ' + I.name + ' ON '+ +SCHEMA_NAME(t.schema_id)+'.'+t.name +';' + CHAR(13) + CHAR(10)  [CreateIndexScript]
FROM sys.indexes I    
JOIN sys.tables T ON T.Object_id = I.Object_id     
JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
AND EXISTS (SELECT *
			FROM sys.index_columns IC1   
			JOIN Sys.columns C    
			   ON C.object_id = IC1.object_id    
			   AND C.column_id = IC1.column_id 
			   AND c.name = @ColumnName
			   AND ic1.index_id = i.index_id
			   AND ic1.object_id = i.object_id) 
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id      
--WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
--AND I.Object_id = object_id('Person.Address') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes  
)
SELECT @dropsql = @dropsql + ISNULL(CreateIndexScript,'')
FROM t1


DECLARE @createsql VARCHAR(MAX)
SELECT @createsql = ''

;WITH t1
AS
(
SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    
    I.name  + ' ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    ' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] ' + ';' + CHAR(13) + CHAR(10)  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id
	   AND c.name = @ColumnName     
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , ' + C.name  
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
--WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
--AND I.Object_id = object_id('Person.Address') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes  
)
SELECT @createsql = @createsql + ISNULL([CreateIndexScript],' ')
FROM t1


PRINT(@dropsql)
PRINT(@createsql)



EXEC(@dropsql)
/*
add your alter code
*/
EXEC(@createsql)

Open in new window

0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 40575537
Commands are very basic. You just need to replace the names in the below example:
DROP INDEX IndexName ON TableName

Open in new window

Don't forget to save the index definition before running the DROP command. You can do that by scripting the index.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40604044
Thanks, guys, for the help.

I looked at the solutions provided by nishant joshi (way over my head) and Vitor (not quite useful enough) and decided to manually go through the process of identifying which table contained indices which contained the field that needed to be resized.

I then simply used the option to script each index, one at a time, to drop and create the index.  Then I pasted the Alter Column command between the drop and create sections of the script and ran the script; it worked like a charm.    I'm certain that this is what the code provided by nishant does, but was not comfortable enough that I understood what was going on with that code to actually use it.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question