?
Solved

Resize varchar column which is used in one or more indices

Posted on 2015-01-27
4
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

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 1400 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 600 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 48

Author Closing Comment

by:Dale Fye
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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