修剪所有数据库字段 [英] Trim all database fields

问题描述

你知道在 sql server 中是否有一种快速的方法(通过 transact-sql)我可以修剪所有数据库字符串字段.

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

推荐答案

无游标.复制并粘贴输出.也适用于没有 varchar(max) 的 SQL 2000.如果需要，这可以很容易地扩展为在每个 UPDATE 的末尾添加一个 GO 行.

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

这篇关于修剪所有数据库字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！