Search code examples
sql-serversql-updatemultiple-tablesinformation-schema

Update value in Multiple Tables Sql Server

I have around 50 tables in my database. In all tables where there is userid column (Not all the tables contain this column), I need to change the value of it from "User1" to "User2". This query would be re-used many times with changing values of "User1" and "User2"

Solution

  • Probably create a stored procedure to do the same like

    create procedure sp_update_table(@tbl_name varchar(30))
as
begin
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'UPDATE ' + QUOTENAME(@tbl_name ) + 
'SET userid='User2' WHERE userid='User1''
EXEC sp_executesql @sql
end

    then just call your procedure as many times you want passing the table name like

    exec sp_update_table('mytable')

    EDIT:

    You can easily find all tables which contains userid column from INFORMATION_SCHEMA.COLUMNS as below

    Use [DatabaseName]
Select table_name From INFORMATION_SCHEMA.COLUMNS Where column_name = 'userid'