Sql Server全文检索
搜索一个库中的所有表，所有列。不知道Sql Server有没有类似的工具，写了一个。有知道的望告知
01
begin
02
-- 要搜索的关键字
03
declare @key varchar(128)
04
set @key = '姚文婷'
05
06
declare cur cursor for
07
select object_id, name from sys.tables
08
open cur
09
10
declare @table_id varchar(255)
11
declare @table_name varchar(255)
12
13
fetch next from cur into @table_id, @table_name
14
15
while @@fetch_status = 0
16
begin
17
declare @sql varchar(max)
18
set @sql = (
19
select 'rtrim(isnull(convert(varchar, ' + name + '), '''')) + ''$'' + '
20
from sys.columns
21
where object_id = @table_id and user_type_id not in(34)
22
for xml path('')
23
)
24
set @sql = 'select ' + @sql + '''ABC'' as Data from ' + @table_name
25
--print @sql
26
27
set @sql = 'declare ccur cursor for ' + @sql
28
29
exec(@sql)
30
31
declare @d varchar(max)
32
33
open ccur
34
35
fetch next from ccur into @d
36
while @@fetch_status = 0
37
begin
38
if (charindex(@key, @d) >
0)
39
begin
40
print 'table: ' + @table_name + ', row: ' + @d
41
end
42
43
fetch next from ccur into @d
44
end
45
close ccur
46
deallocate ccur
47
48
49
fetch next from cur into @table_id, @table_name
50
end
51
52
close cur
53
deallocate cur
54
55
end
1
-- result
2
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 1 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC
3
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 2 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC
4
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 3 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC
5
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 4 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC
6
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 5 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC
7
table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 8 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC