DECLARE @SQL varchar(max)
DECLARE @MyCursor CURSOR
DECLARE @TableName char(50)
DECLARE @FieldName char(50)
--Get a list of all tables in DB
if object_id('tempdb..#temp') is not null
drop table #temp
--45
select o.name TableName,c.name FieldName, t.name datatype , c.max_length , 2 keep
into #temp
from sys.objects o inner join sys.columns c on o.object_id =c.object_id inner join sys.types t on c.user_type_id =t.user_type_id
where type = 'U' and t.precision =0 and t.max_length >0
and o.name not like 'meta%' and o.name not like 'sysd%'
and c.name in ('Address1', 'Address2', 'City', 'Country', 'County', 'Description','FirstName', 'LastName', 'Name', 'JobTitle', 'MiddleName')
--Display the top 3 records of each table
SET @MyCursor = CURSOR FOR
select rtrim(ltrim(TableName)),rtrim(ltrim(FieldName)) from #temp order by fieldname
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @TableName, @FieldName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @fieldname = 'Country'
BEGIN
--SET @SQL = ' SELECT TOP 3 '''+ @fieldname +''', dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') FROM ' + rtrim(ltrim(@TableName) + ' where ' + @fieldname + ' not in (''USA'', ''US'') and len(rtrim(ltrim(' + @fieldname + ')))>2')
SET @SQL = 'UPDATE ' + rtrim(ltrim(@TableName) + 'SET '+ @fieldname +'= dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') where ' + @fieldname + ' not in (''USA'', ''US'') and len(rtrim(ltrim(' + @fieldname + ')))>2')
END
ELSE
BEGIN
--SET @SQL = ' SELECT TOP 3 '''+ @fieldname +''', dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') FROM ' + rtrim(ltrim(@TableName) + ' where ' + @fieldname + ' is not null')
SET @SQL = 'UPDATE ' + rtrim(ltrim(@TableName) + 'SET '+ @fieldname +'= dbo.topropercase(' + rtrim(ltrim(@fieldname)) + ') where ' + @fieldname + ' is not null')
END
--set @sql = 'select '+ @fieldname +' from ' + @TableName
--select @SQL
EXEC(@SQL)
FETCH NEXT FROM @MyCursor
INTO @TableName, @FieldName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
No comments:
Post a Comment