SQL Server: Trim all columns of a table at a time

I had a situation of trimming all columns of a table. My table had 80 columns. So I had to specify each column in the query to trim which was very hectic and irritating.

UPDATE mytable SET col1 = LTRIM(RTRIM(col1)), col2 = LTRIM(RTRIM(col1))... till col80

I found below script as the solution to avoid this.

USE MyDatabase

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'mytable'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
EXEC (@SQL)

When you need to pass table name whcih has to perform LTRIM and RTRIM for all columns just create a stored procedure

CREATE PROCEDURE TrimAllColumnsOfTable @TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

If you have different schema names apart from default dbo schema like [abc].[TableName] and [xyz.pqr].TableName etc. then you must use below SP

CREATE PROCEDURE [dbo].[TrimAllColumnsOfTable] @SchemaName Varchar(100),@TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

We can use this script to trim all char,nchar, varchar and nvarchar columns of all tables across all databases in a server

SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'SET [' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + '])) ' + 'WHERE [' + COLUMN_NAME + '] <> LTRIM(RTRIM([' + COLUMN_NAME + ']))' + CHAR(13) + CHAR(10) + 'GO' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'nvarchar') ORDER BY TABLE_NAME, COLUMN_NAME