MS SQL SERVER 全库搜索

下面是脚本之家 jb51.cc 通过网络收集整理的代码片段。

脚本之家小编现在分享给大家，也给大家做个参考。

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

以上是脚本之家(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得脚本之家网站内容还不错，欢迎将脚本之家网站推荐给程序员好友。

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!