clear spaces from columns in unknow tables.

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 2008 Forums

Transact-SQL (2008)

clear spaces from columns in unknow tables.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-09-08 : 08:11:12

Hi, i currently have to function in vb.net.
One will call a datareader to loop through the columns find with this query:


select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @tablename

and for each datareader row it will run


Update zztesttrim set columname = ltrim(rtrim(columname))

I was wondering if there is a method to automate. I was looking at some code Tara gave me for mergin tables rows in on big table but i am not sure it it will work here.

if it can help the code for that is:


declare @dsql nvarchar(4000), @id int, @rc int
set @id = 1

declare @temp1 table (Customer_name nvarchar(1000), customer_province nvarchar(1000))
declare @DBTempSyncScenario table (TempTableNamev sysname)



INSERT INTO @DBTempSyncScenario
SELECT TempTableName FROM DBTempSyncScenario where firstrown =1



select identity(int, 1, 1) as TempTableId, 'select Customer_name, customer_province from ' + TempTableNamev as TempTableQuery
into #temp2
from @DBTempSyncScenario

set @rc = @@rowcount

while @id <= @rc
begin
	select @dsql = TempTableQuery
	from #temp2
	where TempTableId = @id

	insert into @temp1
	exec (@dsql)

	set @id = @id + 1
end

select Customer_name, customer_province
from @temp1

--drop table @DBTempSyncScenario
--drop table @temp1
drop table #temp2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources