Cari semua tabel yang memiliki kolom nip

 SELECT 
    TABLE_SCHEMA,
    TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'nip';

Generate query untuk mencari NIP tertentu di semua tabel tersebut

 SELECT 
    'SELECT ''' + TABLE_NAME + ''' AS TableName, * FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE nip = ''123456789'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'nip';

Otomatis menjalankan pencarian ke semua tabel

 DECLARE @NIP VARCHAR(50) = '123456789';
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = @SQL +
'
IF EXISTS (
    SELECT 1
    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
    WHERE nip = ''' + @NIP + '''
)
BEGIN
    PRINT ''Ditemukan pada: ' + TABLE_SCHEMA + '.' + TABLE_NAME + ''';
END
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'nip';

EXEC sp_executesql @SQL;

Menampilkan nama tabel yang berisi NIP tersebut

 DECLARE @NIP VARCHAR(50) = '123456789';
DECLARE @SQL NVARCHAR(MAX) = '';

CREATE TABLE #Hasil (
    NamaTabel VARCHAR(256)
);

SELECT @SQL = @SQL +
'
IF EXISTS (
    SELECT 1
    FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']
    WHERE nip = ''' + @NIP + '''
)
INSERT INTO #Hasil
VALUES (''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''');
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'nip';

EXEC sp_executesql @SQL;

SELECT * FROM #Hasil;

DROP TABLE #Hasil;

Mengubah field NIP secara langsung pada beberapa table sekaligus di SQL Server,

Metode I.

 DECLARE @OldNIP VARCHAR(50) = 'NIP_LAMA';
DECLARE @NewNIP VARCHAR(50) = 'NIP_BARU';

DECLARE @TableName NVARCHAR(256);
DECLARE @Sql NVARCHAR(MAX);

-- Cursor untuk mencari semua table yang memiliki kolom 'NIP'
DECLARE curTables CURSOR FOR
    SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = 'NIP';

OPEN curTables;
FETCH NEXT FROM curTables INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Membuat query UPDATE secara dinamis
    SET @Sql = N'UPDATE ' + QUOTENAME(@TableName) + 
               N' SET NIP = @NewNipValue WHERE NIP = @OldNipValue;';
    
    -- Menjalankan query
    EXEC sp_executesql @Sql, 
        N'@OldNipValue VARCHAR(50), @NewNipValue VARCHAR(50)', 
        @OldNIP, @NewNIP;

    FETCH NEXT FROM curTables INTO @TableName;
END

CLOSE curTables;
DEALLOCATE curTables;

Metode II.

BEGIN TRANSACTION;

BEGIN TRY
    -- Update Table 1
    UPDATE Tabel_Karyawan 
    SET NIP = 'NIP_BARU' 
    WHERE NIP = 'NIP_LAMA';

    -- Update Table 2
    UPDATE Tabel_Absensi 
    SET NIP = 'NIP_BARU' 
    WHERE NIP = 'NIP_LAMA';

    -- Update Table 3
    UPDATE Tabel_Gaji 
    SET NIP = 'NIP_BARU' 
    WHERE NIP = 'NIP_LAMA';

    -- Simpan perubahan jika semua sukses
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Batalkan semua perubahan jika terjadi error
    ROLLBACK TRANSACTION;
    PRINT 'Error terjadi, transaksi dibatalkan.';
END CATCH;