SQL Server 2005 - T-SQL to increase field size based on parameters

Question

I'm wondering if there is a way to loop through all of the columns in a table and increase the size of each field based on what the current size. Basically, I need any fields that currently have a field size under 1000 characters to be set to 1000 characters. I can do something like

ALTER TABLE tableName ALTER COLUMN nvarchar(1000)

for each field that I want changed, but there are hundreds of fields and I'd like to do it programmatically if possible. Thanks.

Answer 1

Use this to generate all your ALTER TABLEs

SELECT
    'ALTER TABLE ' +
        OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) +
        ' ALTER COLUMN '+ C.name + ' ' + T.[name] + ' (1000) ' +
        CASE WHEN c.is_nullable = 0 THEN 'NOT' ELSE '' END + ' NULL'
FROM
    sys.columns C
    JOIN
    sys.types T ON C.system_type_id = T.system_type_id
WHERE
    T.[name] LIKE '%varchar' AND C.max_length < 1000
    AND
    OBJECTPROPERTYEX(c.object_id, 'IsMSShipped') = 0

It's easier with the system views.

Answer 2

Here's some code to loop through all of the columns of a named table. It just selects their details; you need to fill out what you do in the loop.

declare @tableName varchar(32)
select @tableName = 'tableName'

declare @i int 
select @i = count(*) from INFORMATION_SCHEMA.COLUMNS
 where Table_Name = @tableName 
while @i > 0 
begin   
  select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = @tableName and Ordinal_Position = @i  
  select @i = @i-1 
end

The fields that you'll be interested in in the INFORMATION_SCHEMA.COLUMNS view are 'DATA_TYPE' and 'CHARACTER_MAXIMUM_LENGTH'

I guess that I'm also making the assumption that the changes you make won't alter the column ordinals, but if they did you could select them in another way.