Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- UPDATE mytable SET mycolumn= LTRIM(RTRIM(mycolumn))
- DECLARE @CRLF AS varchar(2)
- SET @CRLF = CHAR(13) + CHAR(10)
- DECLARE @TAB AS varchar(1)
- SET @TAB = CHAR(9)
- DECLARE @template AS varchar(max)
- SET @template = 'UPDATE {@OBJECT_NAME}' + @CRLF + 'SET {@column_list}'
- DECLARE c CURSOR FAST_FORWARD
- FOR SELECT DISTINCT
- QUOTENAME(T.TABLE_CATALOG) + '.' + QUOTENAME(T.TABLE_SCHEMA)
- + '.' + QUOTENAME(T.TABLE_NAME) AS [OBJECT_NAME]
- FROM INFORMATION_SCHEMA.TABLES AS T
- INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
- ON T.TABLE_CATALOG = C.TABLE_CATALOG
- AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
- AND T.TABLE_NAME = C.TABLE_NAME
- AND T.TABLE_TYPE = 'BASE TABLE'
- AND C.DATA_TYPE IN ('varchar', 'nvarchar')
- ORDER BY 1
- DECLARE @OBJECT_NAME AS sysname
- OPEN c
- FETCH NEXT FROM c INTO @OBJECT_NAME
- WHILE @@FETCH_STATUS = 0
- BEGIN
- DECLARE @column_list AS varchar(max)
- SELECT @column_list = COALESCE(@column_list + @CRLF + @TAB + ',', '')
- + QUOTENAME(C.COLUMN_NAME) + ' = LTRIM(RTRIM('
- + QUOTENAME(C.COLUMN_NAME) + '))'
- FROM INFORMATION_SCHEMA.COLUMNS AS C
- WHERE C.DATA_TYPE IN ('varchar', 'nvarchar')
- AND QUOTENAME(C.TABLE_CATALOG) + '.'
- + QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) = @OBJECT_NAME
- ORDER BY C.ORDINAL_POSITION
- PRINT REPLACE(REPLACE(@template, '{@column_list}', @column_list),
- '{@OBJECT_NAME}', @OBJECT_NAME)
- FETCH NEXT FROM c INTO @OBJECT_NAME
- END
- CLOSE c
- DEALLOCATE c
- ...
- 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
- ...
- 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
Add Comment
Please, Sign In to add comment