Solved

Resize varchar column which is used in one or more indices

Posted on 2015-01-27
4
71 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)
[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 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 50

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I use this function? 3 35
SQL- GROUP BY 4 46
Classic ASP - Display returned sql output parameter 9 51
Complex SQL Server WHERE CLause 9 36
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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