How can I shrink all text columns in a table, so that each column is adjusted to the size of the largest data item it contains?

I work with and process data on a regular basis.

I have found with SQL Server 2000 that when you import a CSV file, all fields get set to the data type varchar and set to length 255 by default.

This of course can take up space unnecessarily on the server. Suppose your imported table has 80 columns. Manually modifying the size of each column can be a pain, plus you have no way of knowing the maximum length of data items in each column without querying the data. Querying each column and then modifying it in turn is going to take quite a lot of time and effort.

Fear not as help is at hand in the form of the stored procedure written below. I hope you will find this useful.


CREATE procedure shrink_Table

@TABLE varchar(100)
AS
-------------------------------------------------------------------------------------------
--Title:shrink_Table
--Author: J.R.Sansom (john.sansom@bcs.org)
--Date:29/09/05
--Description:A simple procedure to shrink the length of text columns of a table
--to the length of maximum data item of each column.
--Requirements:The name of the table passed as a parameter
--
--Example Execution:execute shrink_table @table='tableNAme'
-------------------------------------------------------------------------------------------

SET nocount ON

--Delcare the variables for use with this procedure
declare @sql nvarchar(4000)
declare @ColumnNamevarchar(100)
declare @columnCounter  int
declare @maxColLength varchar(5)
declare @currentColLengthvarchar(5)

declare @catchErrorint
declare @errorCounterint

SET @errorCounter = 0

--Build a SQL statement to create a new table for storing the statistics information.
SET @sql = 'select column_name, null as col_size into lengthStats
from information_schema.columns
where table_name=''' + @TABLE + ''''

--Execute the built statement
print @sql
exec @catchError  = sp_executesql @sql 
SET @errorCounter = @errorCounter + @catchError

--Create a cursor to cycle through all of the columns of the table.
declarecXursor cursor SCROLL FOR
selectcolumn_name
frominformation_schema.COLUMNS
wheretable_name=@TABLE
orderby ordinal_position
opencXursor
fetchnext
fromcXursor
INTO@ColumnName

--Now count the maximum leangth of each of the columns in the table provided as input.
while (@@fetch_status=0 AND @errorCounter=0)
begin
begin transaction

--Create the SQL query to apply the update statement to the current column
SET @sql= 'update lengthStats   
set col_size  = (select max(len( ' + @ColumnName + ' ))
from ' + @TABLE + ')
where column_name = ''' + @ColumnName + ''''
exec @catchError  = sp_executesql @sql 
SET @errorCounter = @errorCounter + @catchError

SET @maxColLength = (SELECT col_size FROM lengthStats WHERE column_name= @columnName)
SET @currentColLength =(SELECT CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE table_name=@TABLE AND column_name=@columnName)

IF((@currentColLength > @maxColLength) AND @maxColLength>0)
begin
--print 'In here!'
SET @sql = 'alter table ' + @TABLE +
   ' alter column ' + @columnName + ' varchar( ' + @maxColLength + ' )'
exec @catchError  = sp_executesql @sql 
print @sql
SET @errorCounter = @errorCounter + @catchError
end

--Fetch the next cursor record
fetch next fromcXursor INTO @ColumnName

IF(@errorCounter=0)
begin
commit
end
else
begin
rollback
print 'Error occured, rollback for the effected transaction completed.'
end

end


--Do a little cleanup
closecXursor
deallocate cXursor
DROP TABLE lengthStats

GO