MSSQLSERVER全库搜索

来源：车融汽车网

定位单个数据库中等于某值的记录所在的表和列。第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 $velocityCount-->

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

显示全文

全部频道

MSSQLSERVER全库搜索