Do be careful here. The fine code that Bhuvnesh posted will have issues if you have gaps in your identity column.

declare @updatedef table ( id int identity, tablename varchar(30), columnname varchar(50), Status_fl bit)

declare @max-2 int ,@cnt int ,@lstr varchar(200),@tablename varchar(30),@columnname varchar(50)

set nocount on

insert into @updatedef (tablename,columnname,Status_fl )

select 'emp', 'fname',1 union

select 'emp', 'age',1 union

select 'emp', 'Phone' ,1 union

select 'EmpSalary', 'Salary' ,1

--Notice what happens here when we delete a row and then insert another to generate a gap in the identity

delete @updatedef where id = 4

insert into @updatedef (tablename,columnname,Status_fl )

select 'EmpSalary', 'Salary' ,1

select @max-2 = MAX(id) from @updatedef

set @cnt = 1

while (@cnt < = @max-2)

begin

select @lstr = '',@tablename = '',@columnname = ''

select @columnname = columnname, @tablename = tablename from @updatedef

where id = @cnt and Status_fl = 1

set @lstr = 'UPDATE dbo.' + @tablename + ' SET ' + @columnname + ' = REPLACE(' + @columnname + ' ''S'', ''t'')'

print @lstr

set @cnt = @cnt + 1

end

Since we are just going to build some dynamic and execute it we can avoid a loop entirely. We can also completely ignore the identity column so gaps there won't be an issue. I also added a new column to @updatedef to indicate if the column is a number type.

declare @updatedef table

(

id int identity,

tablename varchar(30),

columnname varchar(50),

Status_fl bit,

IsNumber bit

)

declare @max-2 int ,@cnt int ,@lstr varchar(200),@tablename varchar(30),@columnname varchar(50)

set nocount on

insert into @updatedef (tablename,columnname,Status_fl, IsNumber )

select 'emp', 'fname', 1, 0 union

select 'emp', 'age', 1, 1 union

select 'emp', 'Phone' ,1, 1 union

select 'EmpSalary', 'Salary' ,1, 1

delete @updatedef where id = 4

insert into @updatedef (tablename,columnname,Status_fl )

select 'EmpSalary', 'Salary' ,1

;with UpdateValues as

(

select 'update dbo.' + tablename + ' set ' as Prefix,

STUFF((select columnname + ' = replace(' + columnname + case when IsNumber = 1 then ', 1, 2)' else ', ''S'', ''t'')' end + ', '

from @updatedef u2

where u2.tablename = u1.tablename

for XML path('')), 1, 0, '') as UpdateColumns

from @updatedef u1

group by tablename

)

select Prefix + left(UpdateColumns, LEN(UpdateColumns) - 1)

from UpdateValues

This will generate the update statements you need to execute. I would just run this, then copy the output and paste it into a new window to run it.