SQL Server Portal

Today, I was searching for some scripts from my scripts library and I came across one of my handy scripts that help me to trim all columns in a table.  You do not need to trim column by column. It will trim all the string columns at once. Given below is the script. I usually use it to clean / massage the data.

SOLUTION :

USE AdventureWorks2012
GO
--DROP PROCEDURE usp_trim_all_string_columns
--GO
CREATE PROCEDURE usp_trim_all_string_columns
@schema_Table_name VARCHAR(MAX)
AS
DECLARE @SQL AS VARCHAR(MAX)

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])
+ ' = LTRIM(RTRIM(' + QUOTENAME([name]) + '))' FROM
sys.columns WHERE object_id=object_id(@schema_Table_name)
AND system_type_id IN(35,99,167,175,231,29)
FOR XML PATH('')),1,1,'')
PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL

EXEC(@SQL)
GO

EXAMPLE :

USE AdventureWorks2012
GO
EXEC usp_trim_all_string_columns '[HumanResources].[Employee]'
GO