编写sqlserver存储过程

写了一个在数据库中查找指定字符串的存储过程。

使用sp_作为过程名的前缀，则可以在任何一个数据库中调用。

代码如下：

--下面这个存储过程用来在某个数据库中的所有表中查找某一字符串 ----使用: EXEC my_Search '张三' CREATE PROCEDURE sp_my_Search_worker @Str nvarchar(100),@SearchFlag int=2,@TableFlag int=1 /** **@Str 要搜索的字符串 **@TableFlag 1: 只在用户表中查找；2：只在系统表中查找;其他:在所有表中查找 **@SearchFlag 1: 精确查询；其他:模糊查询 **/ As begin CREATE table #TableList(tusername sysname,tablename sysname,colname sysname,occurstr nvarchar(200)) declare @tusername sysname declare @table sysname declare @col sysname declare @tuserid sysname declare @truetable sysname set nocount on if @TableFlag=1 declare curTab scroll cursor for select name,uid from sysobjects where xtype='U' and status>0 else if @TableFlag=2 declare curTab scroll cursor for select name,uid from sysobjects where xtype='S' else declare curTab scroll cursor for select name,uid from sysobjects where xtype='S' or xtype='U' open curTab fetch next from curTab into @table,@tuserid --fetch curTab into @tusername,@table set @tusername=USER_NAME(@tuserid) set @truetable=@tusername+N'.'+ @table while @@FETCH_STATUS=0 begin if @table <> 'my_search_result' begin declare curCol scroll cursor for select name from syscolumns where (xtype=175 or xtype=167 or xtype=239 or xtype=231) and (id in (select id from sysobjects where name=@table)) open curCol fetch next from curCol into @col while @@FETCH_STATUS=0 begin if @SearchFlag=1 execute('insert into #TableList select '''+@tusername+''','''+@table+''','''+@col+''','''+@str+''' from '+@truetable+' where '+@col+'='''+@str+'''') else execute('insert into #TableList select '''+@tusername+''','''+@str+''' from '+@truetable+' where '+@col+' like '''+ '%'+@str+ '%'+'''') fetch next from curCol into @col end close curCol deallocate curCol end fetch next from curTab into @table,@tuserid set @tusername=USER_NAME(@tuserid) set @truetable=@tusername+N'.'+ @table end close curTab deallocate curTab set nocount off IF EXISTS(SELECT name FROM sysobjects WHERE name = N'my_search_result' AND type = 'U') insert into my_search_result select * from #TableList ELSE select distinct * from #TableList select count(*) from #TableList drop table #tablelist end

（编辑：李大同）

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