Monday, July 29, 2013

Remove Spaces In Every Column of Given Table

Comments :- 
Exec Ltrim_Rtirm 'Table_name'

I used simple statement in this proc
  UPDATE TABLE NAME
  SET COL1_NAME=LTRIM(RTRIM(COL1_NAME))
  ,COL2_NAME=LTRIM(RTRIM(COL2_NAME))

You can't update an identity column , so i removed identity column in this statement.


Proc :- 

create proc ltrim_rtrim
(
@table_name varchar(200)
)
as
begin
if not exists (select top 1 1 from information_schema.tables where table_name=@table_name)
begin
raiserror('......table not exists.......',16,2)
return
end

declare @qry as varchar(max)
set @qry=(select 'update ' + @table_name + char(10) + ' set ' +
isnull(stuff((select  ',[' + cast(column_name as varchar) + ']='
+ 'ltrim(rtrim(['+ cast(column_name as varchar) + ']))' + char(10)
from information_schema.columns
where table_name=@table_name
and ( column_name in (select column_name
from information_schema.columns
where table_name=@table_name
and column_name not in(select column_name
from information_schema.columns
where table_schema = 'dbo'
and table_name=@table_name
and columnproperty(object_id(table_name), column_name, 'isidentity') = 1)
))
for xml path('')),1,1,''),'''''') )
--print(@qry)
exec(@qry)
end



No comments:

Post a Comment