SQL:在数据库的每个 varchar 列中搜索一个字符串 [英] SQL: search for a string in every varchar column in a database

问题描述

我有一个数据库，其中拼写错误的字符串出现在不同表的不同位置.是否有可用于在数据库中每个可能的 varchar/text 列中搜索此字符串的 SQL 查询?

I have a database where a misspelled string appears in various places in different tables. Is there a SQL query that I can use to search for this string in every possible varchar/text column in the database?

我想尝试以某种方式使用 information_schema 视图来创建动态查询，但我不确定这是否可行，或者是否有更好的方法.

I was thinking of trying to use the information_schema views somehow to create dynamic queries, but I'm not sure if that will work, or if there's a better way.

如果有帮助，我正在使用 MS SQL Server.

I'm using MS SQL Server if that helps.

推荐答案

使用发现的技术 此处 以下脚本为给定数据库中的所有 ((n)var)char 列生成 SELECT.复制/粘贴输出，删除最后一个联合"并执行.您需要将 MISSPELLING HERE 替换为您要查找的字符串.

Using the technique found here the following script generates SELECT's for all ((n)var)char columns in the given database. Copy/paste the output, remove the very last 'union' and execute.. You'll need to replace MISSPELLING HERE with the string you're looking for.

select 
'select distinct ''' + tab.name + '.' + col.name 
+ '''  from [' + tab.name 
+ '] where [' + col.name + '] like ''%MISSPELLING HERE%'' union ' 
from sys.tables tab 
join sys.columns col on (tab.object_id = col.object_id)
join sys.types types on (col.system_type_id = types.system_type_id) 
where tab.type_desc ='USER_TABLE' 
and types.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');

这篇关于SQL:在数据库的每个 varchar 列中搜索一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！