CODEWITHSUNDEEP
sqlsql-serversql-update
Fandango68
Fandango68

Reputation: 4878

SQL Server: how to update tables based on columns that are returned from a query?

I have a query that finds all the tables, across all my databases, with a given "keyword" search:

CREATE TABLE #tblTEMP1
(
     DBaseName VARCHAR(100), 
     TableSchema VARCHAR(50), 
     TableName VARCHAR(100), 
     ColumnName VARCHAR(100), 
     DataType VARCHAR(100),
     CharMaxLength VARCHAR(100) 
)

EXEC sp_MSForEachDB @command1 = 'USE [?];
    INSERT INTO #tblTEMP1 
        SELECT
            Table_Catalog, Table_Schema, Table_Name,
            Column_Name, Data_Type, Character_Maximum_Length
        FROM 
            INFORMATION_SCHEMA.COLUMNS 
        WHERE 
            COLUMN_NAME like ''%password%'''

SELECT * FROM #tblTEMP1

DROP TABLE #tblTEMP1

How do I utilise the above query to actually UPDATE the columns it returns? Yes, I am trying to standardise every "user" table's password column with a single value, for example.

Upvotes: 0

Views: 150

Answers (2)

Zhorov
Zhorov

Reputation: 29983

If I understand you correctly, next approach may help. It's based on your statement, using sp_MSForEachDB undocumented procedure. Temporary table is not needed, just generate and execute a dynamic statement.

EXEC sp_MSForEachDB @command1 = '
    IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN
        USE [?];

        DECLARE @stm nvarchar(max)
        SET @stm = N''''

        SELECT @stm = CONCAT(
            @stm,
            N''UPDATE '',
            QUOTENAME(TABLE_SCHEMA),
            N''.'',
            QUOTENAME(TABLE_NAME),
            N'' SET '',
            QUOTENAME(COLUMN_NAME),
            N'' = ''''your_varchar_value''''; ''
        )
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE COLUMN_NAME LIKE ''password%''

        PRINT @stm
        EXEC (@stm)
    END
'

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

Use sys.databases to get the list of all databases.

  GO

  select * into #dbname from (SELECT * FROM sys.databases) as db

  select * from #dbname

  GO

Then you may use cursor to loop through each database and get your task done.

Upvotes: 0

Related Questions