USE [HRISDEV]

--SELECT * FROM masking 
--SELECT 
--	c.TABLE_SCHEMA,
--	c.TABLE_NAME,
--	c.COLUMN_NAME,
--	c.DATA_TYPE,
--	c.CHARACTER_MAXIMUM_LENGTH 
--FROM
--	INFORMATION_SCHEMA.COLUMNS AS c
--	INNER JOIN
--	SYSOBJECTS AS o ON c.TABLE_NAME = o.name
--WHERE
--	c.TABLE_CATALOG = 'HRISDEV'
--	AND c.COLUMN_NAME LIKE 'NM_PASIEN'
--	AND o.xtype = 'U'
--NM_PEG, NM_REKENING
--NM_IBU, NM_IBU_KDG, NM_KELUARGA, NM_KONTAK_DARURAT SELECT NM_IBU, NM_IBU_KDG FROM SPG_PEGAWAI
--NM_KABAG_KEPEGAWAIAN, NM_PASIEN, NM_PEG_ATASAN_LSG, NM_PEG_HC, SELECT * FROM REC_RIWAYAT_MEDIS
--NM_PEG_PEJABAT
--NM_PEG_PEMBUAT
--NM_PEG_PENERIMA
--NM_PEG_PJB_PENGEMBALIAN
--NM_PEG_PREV
--NM_PEG_PSD
--NM_PEG_UPK
--NM_PEJABAT
--NM_PEJABAT_PETIKAN
--NM_PELAMAR SELECT * FROM SPG_DOC_REC_LETTER
--NM_PESERTA SELECT * FROM SPG_PESERTA
-----------------------------
;DISABLE TRIGGER ALL ON DATABASE;

DECLARE 
	@nip VARCHAR(32),
	@nama VARCHAR(200)

DECLARE list_cursor CURSOR FOR  
SELECT NIP, nama_sesudah
FROM masking 

OPEN list_cursor  
FETCH NEXT FROM list_cursor INTO @nip, @nama

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
			c.TABLE_SCHEMA,
			c.TABLE_NAME,
			c.COLUMN_NAME,
			c.DATA_TYPE,
			c.CHARACTER_MAXIMUM_LENGTH 
		FROM
			INFORMATION_SCHEMA.COLUMNS AS c
			INNER JOIN
			SYSOBJECTS AS o ON c.TABLE_NAME = o.name
			INNER JOIN
			sys.partitions AS p ON o.id = p.object_id
		WHERE
			c.TABLE_CATALOG = 'HRISDEV'
			AND c.COLUMN_NAME IN ('NM_PEG', 'NM_REKENING')	-- cek juga di [HRISDEV].[dbo].tbl_user
			AND o.xtype = 'U'
			AND p.index_id < 2 -- 0:Heap, 1:Clustered 
			AND p.rows > 1
	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
		
		--PRINT '[ ' + @table_name + ' - ' + @column_name + ' ]'
		DECLARE
			@numrow INT = 0,
			@ceknumrow NVARCHAR(400),
			@counter INT = 1

		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 EXISTS(
						SELECT
							*
						FROM
							INFORMATION_SCHEMA.COLUMNS AS c
							INNER JOIN
							SYSOBJECTS AS o ON c.TABLE_NAME = o.name
							INNER JOIN
							sys.partitions AS p ON o.id = p.object_id
						WHERE
							c.TABLE_CATALOG = 'HRISDEV'
							AND c.TABLE_NAME = @table_name
							AND c.COLUMN_NAME = 'NIP'
							AND o.xtype = 'U'
							AND p.index_id < 2 -- 0:Heap, 1:Clustered 
							AND p.rows > 1
						)
					BEGIN
						--SET @sql = 'UPDATE '+@table_name+' SET ['+@column_name+'] = '''+@nama+''' WHERE NIP = '''+@nip+''''
						SET @sql = 'UPDATE '+@table_name+' SET ['+@column_name+'] = '''+REPLACE(@nama, '''', '''''')+''' WHERE NIP = '''+@nip+''''
					END
			END

		--PRINT @sql  -- debug
		EXEC(@sql) -- run
		SET @counter = @counter + 1;

		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_cursor INTO @nip, @nama  
END  

CLOSE list_cursor  
DEALLOCATE list_cursor 

;ENABLE TRIGGER ALL ON DATABASE;