Algunas veces es necesario cambiar un ID en todas las tablas de una bbdd, con este script lo podemos lograr
IF OBJECT_ID ( 'usp_cambioid', 'P' ) IS NOT NULL
DROP PROCEDURE usp_cambioid;
GO
create proc usp_cambioid @idviejo decimal, @idnuevo decimal
as
begin
IF @idviejo IS NULL or @idnuevo is null
BEGIN
PRINT 'ERROR: falta un parametro. use usp_cambioid idviejo, idnuevo'
RETURN
END
begin tran
DECLARE @Tabla varchar(100), @Campo varchar(100), @sql varchar(500), @sqlacu varchar(4000)
declare @count int
set @count = 0
set @sql = ''
set @sqlacu = 'set nocount on' +char(13)+'begin tran '
declare ctablas cursor for
SELECT sysObjects.name as 'Tabla', sysColumns.name as 'Campo'
FROM sysObjects, sysColumns
WHERE sysObjects.id = sysColumns.id and sysObjects.xtype = 'u'
and sysColumns.name = ('id') order by sysObjects.name
open ctablas
FETCH NEXT FROM ctablas into @Tabla, @Campo;
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'update ' + @tabla + ' set ' + @campo + ' = ' +
cast(@idnuevo as varchar(100)) + ' where ' + @campo + ' = ' +
cast(@idviejo as varchar(100))
set @sqlacu = @sqlacu +char(13)+ @sql
FETCH NEXT FROM ctablas into @Tabla, @Campo;
END;
set @sqlacu = @sqlacu + char(13) + 'IF (@@ERROR <> 0) BEGIN ' + char(13) +
' print ''error'''+ char(13) + ' rollback tran ' + char(13) + 'end' + char(13)+
'commit tran'
execute (@sqlacu)
--print @sqlacu
CLOSE ctablas;
DEALLOCATE ctablas;
end
commit tran
