Search code examples
sqldatabasesql-server-2012trim

Trim Spaces inside whole Database

How to trim spaces inside all table's column values from particular Database?

It has to LTrim & RTrim spaces inside row column values from all tables in Database. How Can I do that?

Solution

  • Try below one

         DECLARE @SQL AS VarChar(MAX)
        SET @SQL = ''

         SELECT @SQL = @SQL + 'UPDATE T SET T.'+IC.COLUMN_NAME + 
                          ' = LTRIM(RTRIM(' + IC.COLUMN_NAME+')) 
                          FROM '+ IT.TABLE_SCHEMA + '.[' + IT.TABLE_NAME +
                          '] AS T ;' + CHAR(13)
            FROM INFORMATION_SCHEMA.TABLES IT
            JOIN INFORMATION_SCHEMA.COLUMNS IC ON IT.TABLE_NAME = IC.TABLE_NAME
            AND IT.TABLE_SCHEMA = IC.TABLE_SCHEMA AND IT.TABLE_TYPE = 'BASE TABLE'
and IC.DATA_TYPE in( 'varchar','char','nvarchar','nchar')

        Exec (@SQL)