mysql全库搜索关键字_MS SQL SERVER 全库搜索

定位单个数据库中等于某值的记录所在的表和列。 第13行的xtype=167代表只搜索数据类型是varchar的列。 第18行就是根据关键字具体过滤列的数据。 SQL Server create proc global_search@key varchar(2000)asdeclare tab_cursor cursor for select name from s

定位单个数据库中等于某值的记录所在的表和列。

第13行的xtype=167代表只搜索数据类型是varchar的列。

第18行就是根据关键字具体过滤列的数据。

SQL Server

create proc global_search

@key varchar(2000)

as

declare tab_cursor cursor for select name from sysobjects where type = 'U'

declare @sql nvarchar(2000)

declare @tab_name nvarchar(100)

declare @col_name nvarchar(100)

declare @row_count int

open tab_cursor

fetch next from tab_cursor into @tab_name

while(@@fetch_status = 0)

begin

declare col_cursor cursor for select name from syscolumns where id = OBJECT_ID(@tab_name) and xtype = 167

open col_cursor

fetch next from col_cursor into @col_name

while(@@fetch_status = 0)

begin

set @sql = 'declare row_cursor cursor for select count(*) from ' + @tab_name + ' where ' + @col_name + ' like ''%' + @key + '%'''

exec(@sql)

open row_cursor

fetch next from row_cursor into @row_count

if @row_count > 0

print @tab_name + '.' + @col_name

close row_cursor

deallocate row_cursor

fetch next from col_cursor into @col_name

end

close col_cursor

deallocate col_cursor

fetch next from tab_cursor into @tab_name

end

close tab_cursor

deallocate tab_cursor

create proc global_search

@key nvarchar(2000)

as

declare @sql nvarchar(2000)

declare @tab_name nvarchar(100)

declare @col_name nvarchar(100)

declare @row_count int

declare @has_cursor int

declare @col_cursor cursor

declare @tab_cursor cursor

set @tab_cursor = cursor for select name from sysobjects where type = 'U'

open @tab_cursor

fetch next from @tab_cursor into @tab_name

while(@@fetch_status = 0)

begin

set @col_cursor = cursor for select name from syscolumns where id = OBJECT_ID(@tab_name) and xtype = 231 and length > 13

open @col_cursor

fetch next from @col_cursor into @col_name

while(@@fetch_status = 0)

begin

set @sql = N'select count(*) from ' + @tab_name + ' where ' + @col_name + ' = ''%' + @key + '%'''

exec sp_executesql @sql, N'@row_count int', @row_count

if @row_count > 0

print @tab_name + '.' + @col_name

fetch next from @col_cursor into @col_name

end

if cursor_status('local', '@col_cursor') > -1

close @col_cursor

if cursor_status('local', '@col_cursor') > -3

deallocate @col_cursor

fetch next from @tab_cursor into @tab_name

end

if cursor_status('local', '@tab_cursor') > -1

close @tab_cursor

if cursor_status('local', '@tab_cursor') > -3

deallocate @tab_cursor

本文原创发布php中文网，转载请注明出处，感谢您的尊重！