1
create function dbo.fnc_varchar_columns_alltrim
()
returns @vysledek table
( tabulka sysname null,
sloupec sysname null,
pocet_zaznamu int null,
datovy_typ varchar(256) null
)
as
begin
declare @sql_text nvarchar(2000),
@table_name varchar(256),
@column_name varchar(256),
@dat_typ varchar(256),
@celkem int,
@int_objSQL int,
@int_erros int,
@int_objSelectCountResult int,
@bint_SelectCount bigint
declare @cislo table
( pocet int
)
declare @pom table
( table_name sysname,
column_name sysname,
datovy_typ varchar(256)
)
insert into @pom
select o.name as table_name, c.name as column_name, t.name as datovy_typ
from sys.objects o
left join sys.columns c on c.object_id = o.object_id
left join sys.types t on t.user_type_id = c.user_type_id
where o.type = 'U' and o.name not in ('cVykazPriloha','cVysledovka','cRozvaha','dtprope rties') and o.name not like 'rep%'
and c.user_type_id in (167,231) and c.name not in ('key')
and o.name = 'adm_DB_version' and c.name = 'version'
order by o.name
while exists (select 1 from @pom)
begin
select top 1 @table_name=table_name,@column_name=column_name,@d at_typ=datovy_typ from @pom
delete from @cislo
exec @int_erros = sp_OACreate 'SQLDMO.SQLServer', @int_objSQL OUTPUT
exec @int_erros = sp_OASetProperty @int_objSQL, 'LoginSecure', TRUE
exec @int_erros = sp_OAMethod @int_objSQL, 'Connect', null, '.'
set @sql_text = 'select count(*) from dbo.' + @table_name + ' where len(' + @column_name+ ') <> len(ltrim(rtrim(' + @column_name+ ')))'
set @sql_text = 'ExecuteWithResults("' + @sql_text + '")'
exec @int_erros = sp_OAMethod @int_objSQL, @sql_text, @int_objSelectCountResult OUTPUT
exec @int_erros = sp_OAMethod @int_objSelectCountResult, 'GetRangeString(1, 1)', @bint_SelectCount OUT
exec @int_erros = sp_OADestroy @int_objSQL
set @celkem = @bint_SelectCount
if @int_erros <> 0
exec sp_OAGetErrorInfo @int_objSQL
if @celkem > 0
insert into @vysledek (tabulka,sloupec,pocet_zaznamu,datovy_typ) values (@table_name,@column_name,@celkem,@dat_typ)
delete top (1) from @pom
end
return
end
go
