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 raise the database objects but how can I build the dynamic update for each table that the select returns me?
One possible solution, as stated in the question, is to use the catalog to get the names of the tables that you want to update and to generate a set of update statements that will be finally executed using 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 QUOTENAME as a measure to mitigate potential issues with columns that contain "special characters."
Here's a small example in SQL Fiddle .
Note: This solution assumes that the database does not have a very large number of tables. In this case you can change the solution to use a loop / loop.
Evaluate the use of the sp_MSforeachtable procedure.
-- código #1
USE nome_banco;
EXECUTE sp_MSforeachtable 'UPDATE ? set coluna= valor;'
In the code above, replace coluna with the column name and valor with the expression whose result will be stored in the column. For example, if the column name is Ativo and is to zero the contents of it, we have
EXECUTE sp_MSforeachtable 'UPDATE ? set Ativo= 0;'
The sp_MSforeachtable procedure has some optional usability parameters, which are useful in more complex cases.