Solved

Resize varchar column which is used in one or more indices

Posted on 2015-01-27
4
65 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 46

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl query 19 19
ODBC Driver 11 for SQL Server]Unable to open BCP host data-file 3 54
BULK INSERT most recent CSV 19 42
Filtered index 5 55
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

939 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

6 Experts available now in Live!

Get 1:1 Help Now