Removing trailing spaces from all Varchar columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing trailing spaces from all Varchar columns

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2013-07-24 : 14:54:23

i have this script that generates an update SQL statement when i run on a DB. the Update statement removes all trailing space from all varchar columns in a Database. For some reason when i run this script now it does not generate the update sql statements rather runs some update statement and lists no of rows affected. i have been running this on the same database but i dont know if any of the DB properties had recently changed to not generate the Update sql statement. i run this on other DB and it does generate the update sql statement.

does anybody know why this could happen

begin
declare @meta table(
pk int identity not null,
table_name sysname,
column_name sysname,
space_count int,
update_sql varchar(255))

declare @cmd as nvarchar(1024)
declare @i as int
declare @cnt as int
declare @cnt_spaces as int

insert into @meta(table_name, column_name)
select m.table_name, m.column_name
from (select o.name as table_name, c.name as column_name
from sysobjects o
join syscolumns c on (c.id = o.id)
join systypes t on (t.xtype = c.xtype)
where o.type = 'U'
and t.name = 'varchar') m
order by m.table_name, m.column_name

set @cnt = @@rowcount
set @i = 1

while @i <= @cnt
begin
select @cmd = 'select @cnt_spaces_out = count(*) from ' + table_name + ' t where t.' + column_name + ' like ''% '''
from @meta
where pk = @i

execute sp_executesql @cmd, N'@cnt_spaces_out int output', @cnt_spaces_out = @cnt_spaces output

update @meta
set space_count = @cnt_spaces,
update_sql = 'update ' + table_name + ' set ' + column_name + ' = rtrim(' + column_name + ') where ' + column_name + ' like ''% '''
where pk = @i

set @i = @i + 1
end

select table_name, column_name, space_count, update_sql
from @meta
where space_count > 0
end
go

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 15:27:14

[code]CREATE TABLE #Columns
(
pk INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[schema_name] sysname NOT NULL,
[table_name] sysname NOT NULL,
[column_name] sysname NOT NULL,
space_count int NOT NULL,
update_sql nvarchar(1000) NOT NULL
)

INSERT #Columns
(
[schema_name],
[table_name],
[column_name],
space_count,
update_sql
)
SELECT SCHEMA_NAME(o.uid) AS schema_name,
o.name AS table_name,
c.name AS column_name,
0 AS space_count,
'' AS update_sql
FROM dbo.sysobjects AS o
INNER JOIN dbo.syscolumns AS c on c.id = o.id
INNER JOIN dbo.systypes AS t on t.xtype = c.xtype
WHERE o.type = 'U'
AND t.name LIKE '%varchar'

declare @cmd as nvarchar(1024),
@i as int,
@cnt_spaces as int

SELECT @i = MAX(pk)
FROM #Columns

WHILE @i >= 1
BEGIN
SELECT @cmd = 'SELECT @cnt_spaces_out = count(*) from '
+ QUOTENAME([schema_name]) + '.' + QUOTENAME([table_name])
+ ' WHERE ' + QUOTENAME([column_name]) + ' LIKE ''% '''
FROM #Columns
WHERE pk = @i

EXECUTE sp_executesql @cmd,
N'@cnt_spaces_out INT OUTPUT',
@cnt_spaces_out = @cnt_spaces OUTPUT

IF @cnt_spaces > 0
UPDATE #Columns
SET space_count = @cnt_spaces,
update_sql = 'UPDATE ' + QUOTENAME([schema_name]) + '.' + QUOTENAME([table_name])
+ ' SET ' + QUOTENAME([column_name]) + ' = RTRIM(' + QUOTENAME([column_name]) + ')'
+ ' WHERE ' + QUOTENAME([column_name]) + ' LIKE ''% '''
WHERE pk = @i

SET @i = @i + 1
END

SELECT [schema_name],
[table_name],
[column_name],
[space_count],
[update_sql]
FROM #Columns
WHERE [space_count] > 0[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jayram
Starting Member

47 Posts

Posted - 2013-07-24 : 16:06:45

i get an error- 'SCHEMA_NAME' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-25 : 01:57:42

[code]CREATE TABLE #Columns
(
pk INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[table_name] sysname NOT NULL,
[column_name] sysname NOT NULL,
space_count int NOT NULL,
update_sql nvarchar(1000) NOT NULL
)

INSERT #Columns
(
[table_name],
[column_name],
space_count,
update_sql
)
SELECT o.name AS table_name,
c.name AS column_name,
0 AS space_count,
'' AS update_sql
FROM dbo.sysobjects AS o
INNER JOIN dbo.syscolumns AS c on c.id = o.id
INNER JOIN dbo.systypes AS t on t.xtype = c.xtype
WHERE o.type = 'U'
AND t.name LIKE '%varchar'

declare @cmd as nvarchar(1024),
@i as int,
@cnt_spaces as int

SELECT @i = MAX(pk)
FROM #Columns

WHILE @i >= 1
BEGIN
SELECT @cmd = 'SELECT @cnt_spaces_out = COUNT(*) FROM '
+ QUOTENAME([table_name])
+ ' WHERE ' + QUOTENAME([column_name]) + ' LIKE ''% '''
FROM #Columns
WHERE pk = @i

EXECUTE sp_executesql @cmd,
N'@cnt_spaces_out INT OUTPUT',
@cnt_spaces_out = @cnt_spaces OUTPUT

IF @cnt_spaces > 0
UPDATE #Columns
SET space_count = @cnt_spaces,
update_sql = 'UPDATE ' QUOTENAME([table_name])
+ ' SET ' + QUOTENAME([column_name]) + ' = RTRIM(' + QUOTENAME([column_name]) + ')'
+ ' WHERE ' + QUOTENAME([column_name]) + ' LIKE ''% '''
WHERE pk = @i

SET @i = @i + 1
END

SELECT [table_name],
[column_name],
[space_count],
[update_sql]
FROM #Columns
WHERE [space_count] > 0[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources