update matching column value in anywhere in database sqlserver

I have assetid column in multiple table in my Database and Asset(main) table

I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to 1

update AdminInterests set assetid = 1 where 1020

I just want to how to apply this update for entire database all tables with one query

One possible approach is to generate and execute a dynamic statement using information from system catalog views. It’s important to note, that such an update may fail, if assetid column is a primary key or has constraints in some of the tables.

DECLARE @stm nvarchar(max)

SELECT @stm = (
    SELECT CONCAT(
        ' UPDATE ', QUOTENAME(sch.[name]), '.', QUOTENAME(tab.[name]), 
        ' SET ', QUOTENAME(col.[name]), ' = 1',
        ' WHERE ', QUOTENAME(col.[name]), ' = 1020;'
    )
    FROM sys.columns col
    JOIN sys.tables tab ON col.object_id = tab.object_id
    JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    WHERE col.[name] = 'assetid'
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)') 

PRINT @stm
EXEC sp_executesql @stm

SQL Server (and most other relational database systems) store information about the tables, views, procedures etc. that have been created in a set of internal tables. These are collectively known as the catalog. Specifically there are ones for schemas, tables and columns. We are allowed to read the catalog from code we write.

When a client passes SQL to a server the query is just a text string. So if we can generate a string of the correct format we can pass it to the server and it will be executed. The SELECT statement above generates such a string using the information available in the catalog.

Rather than write a loop that processes one table each pass the code constructs a single string containing all required statements. Each statement is terminated by the semicolon at the end of the literal ' = 1020;'. The statements are concatenated using a trick of how FOR XML constructs its output. In newer versions of SQL Server STRING_AGG could have been used instead.

The print statement is just to aid debugging.

Finally the exec submits the generated string to SQL Server for execution.