Trim all database fields

Question

Do you know if there's a quick way in sql server (via transact-sql) that I could trim all the database string fields.

Answer 1

OK, that was quick and dirty but i have been sufficiently motivated by a current project to do this 'properly' - and with no cursors either, but a little sql concatenation trick. Does use dynamic sql though:

--exec    spGenerateTrimStatements 'StaticImportMaturities'
ALTER   PROCEDURE       spGenerateTrimStatements
    (
            @TableName NVARCHAR(100)
    )
AS
    DECLARE @Cr char(2),    
                    @OutputString nvarchar(max)

    SELECT  @Cr = CHAR(13) + CHAR(10)
    SET     NOCOUNT ON

    -- Create table to store commands
    CREATE  TABLE   #tOutput(OutputText nvarchar(500), RowID int identity(1,1))

    -- Build up commands
    INSERT  #tOutput(OutputText)
    SELECT  'UPDATE ' + @TableName + ' SET ' 

    INSERT  #tOutput(OutputText)
    SELECT  '[' + Column_Name + '] = ' + 'LTRIM(RTRIM([' + Column_Name + '])), ' 
    FROM    INFORMATION_SCHEMA.Columns 
    WHERE   Table_Name = @TableName 
       AND Data_Type LIKE '%CHAR%' 

    --      Trim last comma
    UPDATE  #tOutput
    SET             OutputText = LEFT(OutputText, LEN(OutputText)-1)
    WHERE   RowID = (SELECT Max(RowID) FROM #tOutput)

    -- use subselect to concatenate the command string
    SELECT  @OutputString = ISNULL(@OutputString, '') + ISNULL(OutputText, '')
    FROM (SELECT OutputText
          FROM    #tOutput) TextOutput

    -- run the command
    EXEC sp_ExecuteSQL @OutputString

Answer 2

No cursors. Copy and paste the output. Works also for SQL 2000, which doesn't have varchar(max). This can be easily extended to add a GO line to the end of each UPDATE if desired.

SELECT  SQL
FROM    (       SELECT  t.TABLE_CATALOG
                 ,      t.TABLE_SCHEMA
                 ,      t.TABLE_NAME
                 ,      0               SORT
                 ,      'UPDATE  ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)       SQL
                FROM    INFORMATION_SCHEMA.TABLES       t
                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                   AND  t.TABLE_NAME    = c.TABLE_NAME
                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                GROUP BY        t.TABLE_CATALOG
                 ,              t.TABLE_SCHEMA
                 ,              t.TABLE_NAME
                UNION ALL
                SELECT  x.TABLE_CATALOG
                 ,      x.TABLE_SCHEMA
                 ,      x.TABLE_NAME
                 ,      CASE    WHEN    x.COLUMN_NAME_MIN       = y.COLUMN_NAME
                                THEN    1
                                ELSE    2
                        END                                                     SORT
                 ,      CASE    WHEN    x.COLUMN_NAME_MIN       = y.COLUMN_NAME
                                THEN    'SET     '
                                ELSE    ' ,      '
                        END + y.SQL                                             SQL
                FROM    (       SELECT  t.TABLE_CATALOG
                                 ,      t.TABLE_SCHEMA
                                 ,      t.TABLE_NAME
                                 ,      MIN(c.COLUMN_NAME)      COLUMN_NAME_MIN
                                FROM    INFORMATION_SCHEMA.TABLES       t
                                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                                   AND  t.TABLE_NAME    = c.TABLE_NAME
                                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                                GROUP BY        t.TABLE_CATALOG
                                 ,              t.TABLE_SCHEMA
                                 ,              t.TABLE_NAME
                        )       x
                 JOIN   (       SELECT  t.TABLE_CATALOG
                                 ,      t.TABLE_SCHEMA
                                 ,      t.TABLE_NAME
                                 ,      c.COLUMN_NAME
                                 ,      QUOTENAME(c.COLUMN_NAME) + ' = LTRIM(RTRIM(' + QUOTENAME(c.COLUMN_NAME) + '))'  SQL
                                FROM    INFORMATION_SCHEMA.TABLES       t
                                 JOIN   INFORMATION_SCHEMA.COLUMNS      c
                                  ON    t.TABLE_CATALOG = c.TABLE_CATALOG
                                   AND  t.TABLE_SCHEMA  = c.TABLE_SCHEMA
                                   AND  t.TABLE_NAME    = c.TABLE_NAME
                                WHERE   t.TABLE_TYPE    = 'BASE TABLE'
                                 AND    c.DATA_TYPE     IN ('char','nchar','varchar','nvarchar')
                        )       y
                  ON    x.TABLE_CATALOG = y.TABLE_CATALOG
                   AND  x.TABLE_SCHEMA  = y.TABLE_SCHEMA
                   AND  x.TABLE_NAME    = y.TABLE_NAME
        )       x
ORDER BY        x.TABLE_CATALOG
 ,              x.TABLE_SCHEMA
 ,              x.TABLE_NAME
 ,              x.SORT
 ,              x.SQL

Answer 3

Thanks guys,

Entaroadun code worked pretty well for me, I just had to do some small changes to my requierements, and also I had to reset @colum_list on each iteration.

...
  PRINT REPLACE(REPLACE(@template, '{@column_list}', @column_list),
                          '{@OBJECT_NAME}', @OBJECT_NAME)
    PRINT 'GO'
    SELECT  @column_list = null
            FETCH NEXT FROM c INTO @OBJECT_NAME
...

Answer 4

-- V Quick and Dirty !

-- If this is to generate code to run on a single table, run this code with text output ;

set nocount on

declare @table nvarchar(100) select @table = 'YourTableHere'

SELECT 'UPDATE ' + @table + ' SET ' SELECT '[' + Column_Name + '] = ' + 'LTRIM(RTRIM([' + Column_Name + '])), ' FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = @table AND Data_Type LIKE '%CHAR%'

-- Run as text output (Query/Results To... Results To Text -- Copy and paste the text output (excluding the last comma) into a new query window, and run it .

Answer 5

loop over information_schema.columns and RTRIM the varchar/nvarchar columns by creating the update statement dynamically

Answer 6

Updated the answer of dan to use all tables in the database. Just run the snippet and copy the result to execute.

SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET [' + Column_Name + '] = ' + 'LTRIM(RTRIM([' + Column_Name + ']))' 
FROM INFORMATION_SCHEMA.Columns c
WHERE Data_Type LIKE '%CHAR%'