if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sp_UpdateFields]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sp_UpdateFields]
GO


--Procedure to update a praticular field based on joins through all the Databases Based on EmpID 

CREATE Proc Sp_UpdateFields 
(
@pFieldName VarChar(1000),-- Column name which is require to be updated 
@pTableName Varchar(1000) = '', -- This is optional since the values can be hardcodded 
@pFieldValue Varchar(1000) = '' -- Value for column which is required to be updated 
)
As 
Begin 
Declare @QryString VarChar(8000) 
Declare @TableName VarChar(800)
Declare @ColumnName Varchar(100)
Declare @TblQry Table 
(
QryString Varchar(8000)
)
Insert @TblQry 
Select 'Begin Tran '

select @TableName = '',@ColumnName = ''
While @ColumnName < (Select Max(InRows) From Dbo.CrackInRows(',',@pFieldName))
Begin 
	Select @ColumnName = Min(InRows) From Dbo.CrackInRows(',',@pFieldName) Where InRows > @ColumnName
	Insert @TblQry 
	Select '--Updating the Column ' + @ColumnName 

	select @TableName = ''
	while @TableName < (select max(T.Table_Name) from Information_Schema.Columns As T 
			Inner Join (Select Table_Name From Information_Schema.Columns Where Column_Name = 'EmpID') As F On F.Table_Name = T.Table_Name 
			Inner Join (Select Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE') As K on T.Table_Name = K.Table_Name
			Where T.Column_Name = @ColumnName)
	begin
	select @TableName = Min(T.Table_Name) from Information_Schema.Columns As T 
			Inner Join (Select Table_Name From Information_Schema.Columns Where Column_Name = 'EmpID') As F On F.Table_Name = T.Table_Name 
			Inner Join (Select Table_Name From Information_Schema.Tables Where Table_Type = 'BASE TABLE') As K on T.Table_Name = K.Table_Name
			Where T.Column_Name = @ColumnName And T.[Table_Name] > @TableName
		if (@pTableName <> '')
		 select @QryString = 'Update  T Set '  + @ColumnName + '= ' + @pTableName + '.' + @ColumnName + ' From ' +
			@TableName + ' T Inner Join ' + @pTableName + ' On T.EmpID = '  + @pTableName + '.EmpID'
		Else 
		  select @QryString = 'Update' + @TableName + ' Set '  + @ColumnName + '= ' + @pFieldValue 
	
		Insert @TblQry 
		Select @QryString 
	End
	Insert @TblQry 
	Select '--Updating Completed For Column ' + @ColumnName 
End 
insert @TblQry 
Select 'Rollback Tran '
Select * From @TblQry 
End 

GO