Solved

Resize varchar column which is used in one or more indices

Posted on 2015-01-27
4
64 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
Comment Utility
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
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
Comment Utility
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)
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now