SQLSERVER数据库全局字符搜索_declare tables scroll cursor

前戏

为了提高整个数据库内容的搜索效率,我们要从一下几个方面去优化

1.用游标把数据库的表遍历出来,并一个个表,一个个列去匹配,在遍历表前,需要把无内容的表,或者数据量十分巨大但可能过滤的意义不大的表(某些软件爱写日志,往往几十万,上百万行数据,鼎捷MES半年试运行1.5亿行日志就问你怕不怕)
2. 变量列前,缩小遍历的类型,比如字符只搜索nvarchar或者varchar,如果找不到自行扩大数据类型.
3.希望在搜索的过程中可以一直监视进度,但不能影响搜索效率

上代码

DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @value nvarchar(50)
DECLARE @rows int
DECLARE @count int
DECLARE @index int
set @value ='XX有限责任公司'

set @count=0
if OBJECT_ID(N'tempdb..#t',N'U') is not null drop table #t
 
CREATE TABLE #t (tablename VARCHAR(64),columnname VARCHAR(64),rows int)
DECLARE TABLES scroll CURSOR
FOR
select a.name 表名,b.name 列名,ROW_NUMBER() over(order by a.name,b.name) 序号,c.rows
from sysobjects  a 
inner join syscolumns b on a.id=b.id   
inner join systypes lx on b.xusertype=lx.xusertype
inner join (select id,max(rows) rows  from  sysindexes group by id) c on a.id=c.id
where a.xtype='U' and c.rows>0 and c.rows<1000000  and lx.name ='nvarchar'
order by a.name,b.name
 
OPEN TABLES  FETCH Last FROM TABLES  INTO @table, @column,@count,@rows CLOSE TABLES
print('总数:'+ cast(@count as nvarchar(50)))

OPEN TABLES
FETCH NEXT FROM TABLES INTO @table, @column,@index,@rows
WHILE @@FETCH_STATUS = 0
BEGIN
print(cast(@index as nvarchar(50))+'/'+cast(@count as nvarchar(50))+':' + @table + ',' + @column)
SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] where [' + @column + '] = ''' + @value + ''') INSERT INTO #t VALUES (''' + @table + ''', '''  + @column + ''', '''  +cast(@rows as nvarchar(50))  + ''')'
 EXEC(@sql)
FETCH NEXT FROM TABLES INTO @table, @column ,@index,@rows
END
DEALLOCATE TABLES

SELECT * FROM #t order by rows



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

说明:

• 搜索的过程会在消息里面print出来,有进度和当前正在搜索的表和列名,搜索完了后会输出一张匹配的表和列,还有这个表的数据总量.
• 搜索效率还可以,基本上10秒内能出结果.
• 如果是int或者其他数组类型,最好把转义的单引号去掉