Change Column DataType

Datatype of column may require modification with the new change to the application. Column used in multiple tables could be dynamically changed in one execution using Alter Table command.

ALTER TABLE schemaName.tableName
ALTER COLUMN columnName DataType [NULL|NOT NULL]

The following example increases the size of a varchar\nvarchar column.

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com  
Use DBName
GO
SELECT 'ALTER TABLE [' +sc.name+'].['+object_name(col.object_id)

         +'] ALTER COLUMN ['+ col.name+'] '

+ CASE DATA_TYPE WHEN 'varchar' THEN DATA_TYPE +'(255) NULL ' 

                                  WHEN 'nvarchar' THEN DATA_TYPE +'(175) NULL '

   ELSE ''  END

,col.*

FROM sys.columns col

INNER JOIN sys.tables tb ON col.[object_id]=tb.[object_id]

INNER JOIN sys.schemas sc ON sc.[schema_id]=tb.[schema_id]

INNER JOIN INFORMATION_SCHEMA.COLUMNS incol ON incol.TABLE_NAME=tb.name

                                            AND incol.TABLE_SCHEMA=sc.name

                                            AND incol.COLUMN_NAME=col.name



WHERE DATA_TYPE in ('varchar','nvarchar') AND col.name ='CaseDetail'

AND CHARACTER_MAXIMUM_LENGTH<255

Note: 

1. Reducing the precision or scale of a column may cause data truncation. 
2. Column contain data, its new size should be equal to or greater than the old size. 
3. Read modified column cannot be any of on Link