Sql-server – update matching column value in anywhere in database sqlserver

sql serverupdate

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

Best Answer

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.

Related Solutions

SQL Server – How to Set Default Value for All Columns with Specific Name

Let's say the new default you want is 5:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
  ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c 
  + ' DEFAULT (5) FOR [project_id];'
FROM
(
  SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name), 
    c = QUOTENAME(d.name)
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.default_constraints AS d
  ON d.parent_object_id = t.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE c.name = N'project_id'
) AS x;

PRINT @sql;
--EXEC sp_executesql @sql;

Notes:

print is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
This will only add the constraint to project_id columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
This will not change the values already generated based on the old defaults.

Here is why I recommend against INFORMATION_SCHEMA for this type of problem:

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views

Sql-server – always error “cannot insert value NULL” sqlserver

First observation is that your update statement has no WHERE clause. This means that it will update all rows of the table. Are you sure you want that?

From the comments, it seems that all rows have year_period = 2016 and you do want to update all of them. In my opinion, it wouldn't hurt to explicitly add this condition in the statement. The opposite, it would avoid any unwanted changes, in case you run it in the future, say next year when you have rows with 2016 and 2017.

Second, the error is due to for some rows, either the subquery returns a NULL value or it return no rows at all.

You can easily find the "missing" values (those that the subquery returns no rows) with this:

SELECT DISTINCT tr_kpi_id
FROM tmpiscunitmap AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM    tmptr_kpi AS a
            INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
        WHERE
            a.year_period = 2015 AND
            b.year_period = 2016 AND
            a.tr_kpi_id = t.tr_kpi_id
      ) 
--  AND year_period = 2016 
 ;

Best Answer

Related Solutions

SQL Server – How to Set Default Value for All Columns with Specific Name

Sql-server – always error “cannot insert value NULL” sqlserver

Related Question