mysql全表搜索内容,SQL Server全库搜索（在所有表中查找内容）

SQL Server全库搜索(在所有表中查找内容)

SQL Server版本：SQL Server2008

某个内容到底存储在数据库的哪个地方？无从下手时，可以使用全库查找。

SQL Server在整个库的所有表的所有字段中查找内容，用到了临时表，游标循环。

declare @Str nvarchar(max), @tableName varchar(50), @colName varchar(50), @rowCount int

select a.name tableName, b.name Colname, 0 as IsFound into #t1

from sysobjects a join syscolumns b on a.id=b.id join systypes c on b.xtype=c.xtype

where a.[type]=‘U‘ and c.name in (‘varchar‘, ‘nvarchar‘, ‘char‘, ‘nchar‘) --这里是设置字段的类型，以缩小范围

declare _c1 cursor for select Colname, tableName from #t1

open _c1

fetch next from _c1 into @colName, @tableName

while @@FETCH_STATUS=0 begin

--print @Str

select @Str=‘select @rowCount=count(1) from [‘[email protected]+‘] where [‘[email protected]+‘] like ‘‘%TotalDsc%‘‘‘ --这里是要查找的内容

exec sp_executesql @Str, N‘@rowCount int output‘, @rowCount output

if @rowCount>0 update #t1 set IsFound=1 where [email protected] and [email protected]

fetch next from _c1 into @colName, @tableName

end

close _c1

deallocate _c1

select * from #t1 where IsFound=1

drop table #t1

搜索出来的结果解释：tableName：表名Colname：列名IsFound：找到的个数再也不怕找不到在数据库的哪个地方了。