Sp_MSForeachTable to update tables having common columns

Suppose we have some tables in our database out of which some tables have common columns.

Say there are 3 tables (e.g. tbl1, tbl2,tbl3) out of which tbl1 and tbl2 has got a common column between themselves.

The task is to update the values of all the tables which has got the common columns.

N.B.~ The situation is that, there is no referential integrity constaint in the tables and henceforth we cannot apply ON UPDATE CASCADE option.

There are many ways of doing this but here we will show how we can achieve this using the undocumented stored procedure sp_msforeachtable

Let us first create the test environment by executing the below script

/*
	 Check if the table exists. If so drop them and recreate; else create
*/
If Exists (Select * From Sys.Objects Where Name = N'tbl1' And Type = 'U') Drop Table tbl1
Go
Create Table [dbo].[tbl1]
(
	[CommonColumn] [varchar](50) NULL
	,[Name] [varchar](50) NULL
)
If Exists (Select * From Sys.Objects Where Name = N'tbl2' And Type = 'U') Drop Table tbl2
Go
Create Table [dbo].[tbl2](
	[CommonColumn] [varchar](50) NULL
	,[Name] [varchar](50) NULL
	,[Age] [int] NULL
) 
If Exists (Select * From Sys.Objects Where Name = N'tbl3' And Type = 'U') Drop Table tbl3
Go
Create Table [dbo].[tbl3](	
	[Name] [varchar](50) NULL
	,[Age] [int] NULL
) 
/* 
	Insert records into respective tables
*/
 
Insert Into tbl1
Select 'A111', 'Name10' Union All
Select 'A222', 'Name11' Union All
Select 'A333', 'Name12' Union All
Select 'A444', 'Name14'
 
Insert Into tbl2 
Select 'A111', 'Name20', 20 Union All
Select 'A222', 'Name21', 21 Union All
Select 'A333', 'Name22', 22 Union All
Select 'A444', 'Name23', 23
Insert Into tbl3 
Select 'Name30', 30 Union All
Select 'Name31', 31 Union All
Select 'Name32', 32 Union All
Select 'Name33', 33
 
/* 
	Display the records
*/
 
Select * from tbl1
/*
CommonColumn	Name
A111			Name10
A222			Name11
A333			Name12
A444			Name14
*/
Select * from tbl2
/*
CommonColumn	Name	Age
A111			Name20	20
A222			Name21	21
A333			Name22	22
A444			Name23	23
*/
Select * from tbl3
/*
Name	Age
Name30	30
Name31	31
Name32	32
Name33	33
*/

Now run the below script

Exec sp_msforeachtable N'
  If Exists(
				Select 1 
				From Sys.Columns 
				Where Object_Id=Object_Id(''?'') 
				And Name = ''CommonColumn''
			)
	  Exec (''
				Update ? 
				Set CommonColumn = 
			Case When CommonColumn = ''''A111'''' Then ''''New111'''' 
			     When CommonColumn = ''''A222'''' Then ''''New222'''' 
			     When CommonColumn = ''''A333'''' Then ''''New333'''' 
			     When CommonColumn = ''''A444'''' Then ''''New444'''' 
			End
		   '')'
/* 
	Display the records
*/
 
Select * from tbl1
/*
CommonColumn	Name
New111			Name10
New222			Name11
New333			Name12
New444			Name14
*/
Select * from tbl2
/*
CommonColumn	Name	Age
New111			Name20	20
New222			Name21	21
New333			Name22	22
New444			Name23	23
*/
Select * from tbl3
/*
Name	Age
Name30	30
Name31	31
Name32	32
Name33	33
*/

The query is searching in the system table (here Sys.Columns) for those table objects which contains a column by the name “CommonColumn” and once found it updates the table value.

Hope this helps.