如何查找包含字符串和空值的所有列
问题描述:
我的目标是编写一个SQL Server脚本(2008 R2,如果它很重要),将列名称中包含“限定符”的所有表中的所有值设为零。如何查找包含字符串和空值的所有列
E.g.表A包含名为“TemperatureQualifiers”和“SalinityQualifiers”的列。对于该表中的所有行,应将这些值设置为空。还有其他几个表具有名称相似的列。
答
这将为您生成更新语句。您可以扩展它以执行它们作为动态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'
答
这一点迟到了。这将生成一个脚本,用于合并更新,以更新同一个表中有多个列的更新。
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('')
Exec(@SQL)这将执行动态sql。 – 2010-09-10 14:02:31
太棒了!正是我在找什么。 – KevDog 2010-09-10 14:10:54
引用额外zing的名字。 – 2010-09-10 14:11:07