How to make a dynamic update to all database tables for a specific column

Asked

Viewed 1,084 times

1

Personal how can I make a dynamic update to all database tables for a specific column common to all my tables?

I’m thinking of using the catalog table to pull objects from the database but how can I build the dynamic update for each table that select returns me?

sql-server server tsql

  • try to explain in more detail your problem. Perhaps it is better to put a piece of code than ever to help understanding.

  • Look for examples of Triggers and Cursors, but I don’t know if SQL-Server has it, most databases don’t use it because it’s heavy, another way is to create controllers (in code and out of the database) to perform all database operations on your system, then just upgrade using it

2 answers

1

A possible solution, as stated in the question, is to use the catalog to get the names of the tables you want to update and generate a set of update instructions that will finally be executed using the procedure sp_executesql. For example:

DECLARE @nomeColuna NVARCHAR(50) = 'aMinhaColuna'    -- nome da coluna que pretende actualizar
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'UPDATE ' + t.name + ' SET ' + @nomeColuna + ' = ''QualquerCoisa'';'
  FROM sys.columns c
 INNER JOIN sys.tables t 
    ON c.object_id = t.object_id
 WHERE c.name = @nomeColuna 

EXEC sp_executesql @sql

Additionally, I recommend using the QUOTENAME as a measure to mitigate potential problems with columns containing "special characters".

Here is a small example in SQL Fiddle.

Note: This solution assumes that the database does not have a very high number of tables. In this case you can change the solution to use a loop/loop.

0

Evaluate the use of the sp_MSforeachtable procedure. 

-- código #1
USE nome_banco;

EXECUTE sp_MSforeachtable 'UPDATE ? set coluna= valor;'

In the above code replace coluna by the name of the column and valor by the expression whose output will be stored in the column. For example, if the column name is Ativo and it’s to reset the contents of it, we have

EXECUTE sp_MSforeachtable 'UPDATE ? set Ativo= 0;'

The procedure sp_MSforeachtable has some optional usage parameters, which are useful in more complex cases.

Browser other questions tagged sql-server server tsql

You are not signed in. Login or sign up in order to post.