代码之家 › 专栏 › 技术社区 › KevDog

如何查找包含字符串的所有列并将这些值设为Null

sql-server

KevDog · 技术社区 · 15 年前

我的目标是编写一个sqlserver脚本(如果有必要的话,可以是2008r2),在列名包含“限定符”的所有表中清空所有值。

例如,表A包含名为“TemperatureEqualifiers”和“SalinityQualifiers”的列。对于该表中的所有行,这些值都应设置为null。还有其他几个表具有具有类似名称的列。

2 回复 | 直到 15 年前

Joe Stefanelli 15 年前

这将为您生成update语句。您可以将其扩展为作为动态SQL执行,或者简单地将结果剪切/粘贴到另一个SSMS查询窗口并运行它们。

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'

Martin Smith 15 年前

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('')

推荐文章