SQL:在数据库的每个varchar列中搜索字符串

使用发现的技术 here 下面的脚本为给定数据库中的所有((n)var)char列生成select。复制/粘贴输出,删除最后一个“union”并执行..您需要用您要查找的字符串替换这里的拼写错误。

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搜索工具呢?ApexSQL有 ApexSQL Search 还有 SQL Search 来自红门。这两项工作都能轻松完成。

您可以使用光标和sys.tables/sys.columns视图来浏览它们。给我一分钟,我给你密码。

更新:这里是:

declare @col_name nvarchar(50)
declare @sql nvarchar(max)
declare @tbl_name nvarchar(50)
declare @old_str nvarchar(50)
declare @new_str nvarchar(50)

set @old_str = 'stakoverflow'
set @new_str = 'StackOverflow'

declare fetch_name cursor for
select 
    c.name,
    t.name
from 
    sys.columns c
    inner join sys.tables t on c.object_id = t.object_id
    inner join sys.types y on c.system_type_id = y.system_type_id
where
    y.name like '%varchar'
    or y.name like '%text'

open fetch_name

fetch next from fetch_name into @col_name, @tbl_name

while @@fetch_status = 0
begin
    set @sql = 'UPDATE ' + @tbl_name + ' SET ' + 
        @col_name + ' = replace(' + 
            @col_name + ',''' + 
            @old_str + ''',''' + 
            @new_str + ''')'

    exec sp_executesql @sql

    fetch next from fetch_name into @col_name
end

close fetch_name
deallocate fetch_name

这会给你所需要的一切。它从数据库中获取varchar、nvarchar、text和ntext列,循环浏览这些列并更新每个列。

当然,您也可以这样做来创建一个连接的SQL语句,并在末尾进行一次大的更新,但是,这是您的首选。

据我所知,我不喜欢光标,但是因为我们处理的是一些列,而不是数百万行,所以我对这一行没意见。

上述脚本的SQL Server 2000版本(来自edosoft):

select  
'select distinct ''[' + tab.name + ']'' as TableName, ''[' + col.name + ']'' as ColumnName'
+ ' from [' + users.name + '].[' + tab.name  
+ '] where UPPER([' + col.name + ']) like ''%MISSPELLING HERE%'' union '  
from sysobjects tab  
join syscolumns col on (tab.id = col.id) 
join systypes types on (col.xtype = types.xtype)  
join sysusers users on (tab.uid = users.uid)
where tab.xtype ='U'  
and types.name IN ('char', 'nchar', 'varchar', 'nvarchar'); 

如果有人需要这样的东西来支持Sybase,那么下面的内容可能会有所帮助。

我创建了下面的脚本,代码打印出所有的表名、包含搜索字符串的列名。

不优化性能,使用光标在DB列中循环,因此在大DB上运行它可能需要一段时间(取决于大小、表/列的数量等)。

不过,我认为在数据库中搜索字符串是一个很好的实用程序。

-----------------------------------------------------------------------------------------------------
-- SYBASE - SCRIPT TO FIND STRING IN ANY COLUMN IN TABLE AND PRINT TableName/ColumnName TO RESULTS --
-----------------------------------------------------------------------------------------------------

-- tested on Sybase ASE 15.7

set nocount off

-- CREATE OBJECTS REQUIRED FOR SCRIPT
create table #SearchString (SearchString varchar(100))
go

-- SET SEARCH STRING
declare @search_string  varchar(100)
set @search_string = 'SEARCH_STRING'

-- WRITE SEARCH STRING TO TEMP TABLE TO STORE IT AWAY AND BE ABLE TO READ IT IN NEXT BATCH
insert into #SearchString (SearchString)
    values (@search_string)

-- GET ALL RELEVANT TABLES AND COLUMNS
insert #TabCol
    select object_name(o.id) as TableName, c.name as ColumnName
        from sysobjects o, syscolumns c 
    where o.type = 'U' -- ONLY USER TABLES
          and c.usertype in (1,2,18,19,24,25,42) -- ONLY LOOK FOR CHAR, VARCHAR, ETC.
          and c.id = o.id
          and c.name is not null
          and c.length >= datalength(@search_string)
go

-- GET TOTAL NUMBER OF RELEVANT COLUMNS
select count(*) as RelevantColumns from #TabCol
go

-- CREATE CURSOR TO LOOP THROUGH TABLES AND COLUMNS TO FIND COLUMNS CONTAINING THE SEARCH STRING
declare cur cursor for 
select TableName, ColumnName from #TabCol order by TableName, ColumnName
for read only
go

-- VARIABLE DEFINITION
declare
    @table_name     SYSNAME,
    @table_id       int,
    @column_name    SYSNAME,
    @sql_string     varchar(2000),
    @search_string  varchar(100)

-- GET SEARCH STRING FROM TABLE
select @search_string = SearchString from #SearchString

-- CURSOR INIT
open cur

fetch cur into @table_name, @column_name

-- LOOP THROUGH TABLES AND COLUMNS SEARCHING FOR SEARCH STRING AND PRINT IF FOUND
while (@@sqlstatus != 2)
begin
    set @sql_string = 'if exists (select * from ' + @table_name + ' where [' + @column_name + '] like ''%' + @search_string + '%'') print ''' + @table_name + ', ' + @column_name + ''''
    execute(@sql_string)
    fetch cur into @table_name, @column_name
end
go

-- CLEAN-UP
close cur
deallocate cur

drop table #SearchString
drop table #TabCol
go

干杯

我把这个模式加入了江户软件的版本中。

select 
'select distinct ''[' +  SCHEMA_NAME(tab.schema_id) + '].[' + tab.name + '].[' + col.name + ']'
+ '''  from [' +  SCHEMA_NAME(tab.schema_id) + '].[' + tab.name 
+ '] where [' + col.name + '] like ''%hsapp%'' 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');

select column_name from information_schema.columns 
    where table_name ='magazines' and DATA_TYPE IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR');

希望它有帮助