How Can I Find All Columns That Contain A String and Null Those Values

Question

My goal is to write a SQL Server script (2008 R2 if it matters) that nulls out all values in all tables where the column name contains "Qualifiers".

E.g. Table A contains columns named "TemperatureQualifiers" and "SalinityQualifiers". For all rows in that table, those values should be set to null. There are also several other tables that have columns with similar names.

Answer 1

This will generate the update statements for you. You can extend this to execute them as dynamic SQL or simply cut/paste the results to another SSMS query window and run them.

select 'update [' + s.name + '].[' + t.name + '] set [' +  c.name + '] = NULL'
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
        inner join sys.schemas s
            on t.schema_id = s.schema_id
    where c.name like '%Qualifiers%'
        and t.type = 'U'

Answer 2

Bit late on this one. This will generate a script that consolidates updates where there are multiple columns in the same table to be updated.

DECLARE @Script nvarchar(MAX);
SET @Script = '';
WITH Cols AS
     ( SELECT c.object_id,
              c.name,
             schema_name(t.schema_id) AS SchemaName,
             t.name AS TableName
     FROM    sys.columns c INNER JOIN
                      sys.tables t ON c.object_id = t.object_id
     WHERE   c.name LIKE '%Qualifiers%'
     AND     is_computed=0
     AND     is_rowguidcol=0
     AND     is_identity=0
     AND     is_nullable=1
     AND     objectproperty(c.object_id, N'IsUserTable')=1
     )
     ,
     Tables AS
     ( SELECT DISTINCT object_id, TableName, SchemaName
       FROM Cols
     )
     ,
     Statements AS
     ( SELECT 'UPDATE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' SET ' + STUFF(
             ( SELECT ',' + c.name + '=NULL'
             FROM    Cols c
             WHERE   c.object_id = t.object_id FOR XML PATH('')
             )
             , 1, 1, '') AS Statement
     FROM    Tables t
     )
SELECT @Script = @Script + ' 
' +Statement
FROM   Statements
SELECT @Script AS [processing-instruction(x)] FOR XML PATH('')