DECLARE @listcol TABLE ( colname VARCHAR(30))

-- tulis kolom-kolom yang akan diubah isinya disini
-- bisa tidak lengkap, karena menggunakan LIKE
INSERT INTO @listcol (colname) VALUES ('rekening')
INSERT INTO @listcol (colname) VALUES ('nm_peg')
INSERT INTO @listcol (colname) VALUES ('nm_keluarga')
INSERT INTO @listcol (colname) VALUES ('lahir')
INSERT INTO @listcol (colname) VALUES ('alamat')
INSERT INTO @listcol (colname) VALUES ('telp')
INSERT INTO @listcol (colname) VALUES ('handphone')
INSERT INTO @listcol (colname) VALUES ('induk_kependudukan')

DECLARE @colname VARCHAR(30)
DECLARE list_col CURSOR FAST_FORWARD FOR 
SELECT colname
FROM @listcol
OPEN list_col
FETCH NEXT FROM list_col INTO @colname
WHILE @@FETCH_STATUS = 0 
BEGIN
	DECLARE
		@table_schema VARCHAR(50), 
		@table_name VARCHAR(50), 
		@column_name VARCHAR(50), 
		@data_type VARCHAR(50),
		@character_maximum_length INT
	DECLARE list_col_tab CURSOR FAST_FORWARD FOR 
		SELECT
			TABLE_SCHEMA,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE,
			CHARACTER_MAXIMUM_LENGTH 
		FROM
			INFORMATION_SCHEMA.COLUMNS 
		WHERE
			TABLE_CATALOG = 'HRIS'
			AND COLUMN_NAME LIKE '%' + @colname + '%'
	OPEN list_col_tab
	FETCH NEXT FROM list_col_tab INTO @table_schema, @table_name, @column_name, @data_type, @character_maximum_length
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		DECLARE
			@numrow INT = 0,
			@ceknumrow NVARCHAR(400),
			@counter INT = 1
		IF @data_type = 'varchar' 
			SET @ceknumrow = N'SELECT @numrow = COUNT(*) FROM ['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> '''''
		ELSE
			SET @ceknumrow = N'SELECT @numrow = COUNT(*) FROM ['+@table_name+'] WHERE ['+@column_name+'] IS NOT NULL'
		EXEC sp_executesql @ceknumrow, N'@numrow INT OUTPUT', @numrow OUTPUT;
		WHILE @counter <= @numrow 
		BEGIN
			DECLARE @sql VARCHAR(400)
			IF @data_type = 'varchar'
				BEGIN
					DECLARE
						@rand_str VARCHAR(20),
						@max_length INT,
						@retval_sp_randstr INT,
						@po_text VARCHAR(50)
					IF @character_maximum_length>20 SET @max_length = 20 -- batasin agar tidak terlalu panjang
						EXEC @retval_sp_randstr = dbo.proc_generate_random_string 
												@pi_min = 5,
												@pi_max = @max_length,
												@po_text = @rand_str OUTPUT
						SET @sql = 'UPDATE y
								   SET ['+@column_name+'] = '''+@rand_str+'''
								   FROM (
									SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x
									FROM ['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> ''''
									) y
								   WHERE
								    y.x = '+CAST(@counter AS VARCHAR(10))
				END
			ELSE IF @data_type = 'date'
				BEGIN
					DECLARE
						@random_diff_date VARCHAR(10),
						@rand_upper INT = -100, -- batas min
						@rand_lower INT = 100 -- batas max
					SELECT @random_diff_date = CAST(ROUND(((@rand_upper - @rand_lower -1) * RAND() + @rand_lower), 0) AS VARCHAR(10))
					SET @sql = 'UPDATE y
								SET ['+@column_name+'] = DATEADD(DAY, '+@random_diff_date+', ['+@column_name+'])
								FROM (
								SELECT ['+@column_name+'], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS x
								FROM ['+@table_name+'] WHERE COALESCE(['+@column_name+'],'''') <> ''''
								) y
								WHERE
								y.x = '+CAST(@counter AS VARCHAR(10))
				END
			PRINT @sql  -- debug
			--EXEC(@sql) -- run
			SET @counter = @counter + 1;
		END;
FETCH NEXT FROM list_col_tab INTO @table_schema, @table_name, @column_name, @data_type, @character_maximum_length
END
CLOSE list_col_tab 
DEALLOCATE list_col_tab
FETCH NEXT FROM list_col INTO @colname 
END
CLOSE list_col 
DEALLOCATE list_col