SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Removing trailing spaces from all Varchar columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

47 Posts

Posted - 07/24/2013 :  14:54:23  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 07/24/2013 :  15:27:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 07/24/2013 :  16:06:45  Show Profile  Reply with Quote
i get an error- 'SCHEMA_NAME' is not a recognized function name.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 07/25/2013 :  01:57:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000