CREATE procedure [dbo].[usp_TrimColsTest]( @TName varchar(500))
as

declare currentColumn cursor for
select column_name
from information_schema.columns 
where table_name=@Tname
order by ordinal_position asc

open currentColumn

declare @cName varchar(500)
declare @sqlstring nvarchar(255)
declare @Tcnt int
DECLARE @ParmDefinition nvarchar(500)


fetch next from currentColumn into @cName
while @@fetch_status=0 
begin

	set @sqlstring = 'update ['+@Tname+'] set ['+@cname+']=ltrim(rtrim(['+@cname+']))'
	SET @ParmDefinition = N'@TcntOUT varchar(30) OUTPUT';
	EXECUTE sp_executesql @SQLString , @ParmDefinition, @TcntOut=@Tcnt output

	fetch next from currentColumn into @CName
end
close currentColumn
deallocate currentColumn

CREATE PROCEDURE [dbo].[usp_TrimColsTest]( @TName VARCHAR(500)) AS
DECLARE @sql NVARCHAR(MAX)='UPDATE ' + QUOTENAME(ISNULL(PARSENAME(@Tname,2),'dbo')) + '.' + QUOTENAME(PARSENAME(@Tname,1)) + ' SET '

SELECT @sql=@sql+REPLACE('?=LTRIM(RTRIM(?)),', '?', QUOTENAME(column_name))
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME=PARSENAME(@Tname,1) AND TABLE_SCHEMA=ISNULL(PARSENAME(@Tname,2),'dbo')
AND DATA_TYPE LIKE '%char' AND COLUMNPROPERTY(OBJECT_ID(@Tname),COLUMN_NAME,'IsComputed')=0

SET @sql=LEFT(@sql,LEN(@sql)-1)  -- trim trailing comma from SQL statement

EXEC(@sql)

quote:

---

Originally posted by robvolk

I agree with Visakh's suggestion, but if you absolutely need it

---