How can I shrink all text columns in a table?

Dialect: T-SQL
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?

Dialect: MS SQL
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.

SQL:

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 @ColumnName		varchar(100)

	declare @columnCounter  	int

	declare @maxColLength 		varchar(5)

	declare @currentColLength	varchar(5)

	

	declare @catchError	int 

	declare @errorCounter	int 



	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.

	declare	cXursor cursor SCROLL for

		select	column_name

		from	information_schema.columns

		where	table_name=@table

		order	by ordinal_position

		open	cXursor

		fetch	next

		from	cXursor

		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 from	cXursor 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

	close	cXursor

	deallocate cXursor

	drop table lengthStats



GO