老大写的,收藏珍品:if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[p_search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_search] GO/*--搜索某个字符串在那个表的那个字段中 --邹建 2004.10(引用请保留此信息)--*//*--调用示例 use pubs exec p_search N'l' --*/ create proc p_search @str Nvarchar(1000) --要搜索的字符串 as if @str is null returndeclare @s Nvarchar(4000) create table #t(表名 sysname,字段名 sysname)declare tb cursor local for select s='if exists(select 1 from ['+replace(b.name,']',']]')+'] where ['+a.name+'] like N''%'+@str+'%'') print ''所在的表及字段: ['+b.name+'].['+a.name+']''' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xtype in(175,239,99,35,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tb go
if exists (select * from dbo.sysobjects where id = object_id('p_replace')) drop procedure [dbo].[p_replace] goif exists(select * from sysobjects where id=object_id('#')) drop table # goset nocount on go--创建存储过程 create proc p_replace @colname Nvarchar(1000), --要搜索的字段 @rstr nvarchar(200) --要替换的内容 as if @colname is null return declare @s Nvarchar(4000) select a.name col,b.name tb into # from syscolumns a join sysobjects b on a.id=b.id and a.name like '%'+@colname+'%' declare cur cursor for select * from # open cur declare @tbname nvarchar(20) declare @columnname nvarchar(20) fetch next from cur into @columnname,@tbname while @@fetch_status=0 begin set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+'''' --print(@s) exec(@s) fetch next from cur into @columnname,@tbname end close cur deallocate cur go--测试存储过程 exec p_replace N'adduser',N'hhhhhhhhhhhhh'
楼上的vivianfdlpw() 的方法不行 都没有改过来。
楼上的vivianfdlpw() 你能说清楚点吗?我试了还是不行啊
楼上的vivianfdlpw() 你的程序我改了一点 由 begin set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+'''' --print(@s) exec(@s) fetch next from cur into @columnname,@tbname end 改成 begin set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+''' where '+@columnname+'='''+@colname+'''' print(@s) exec(@s) fetch next from cur into @columnname,@tbname end 加了一个where条件,基本可以,但是还有点问题,就是没有@rstr值的表也给出来了,只要有a.name 他就显示,但问题是有a.name字段并不一定有@str值,如何过滤掉这些值呢
select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_search]
GO/*--搜索某个字符串在那个表的那个字段中
--邹建 2004.10(引用请保留此信息)--*//*--调用示例
use pubs
exec p_search N'l'
--*/
create proc p_search
@str Nvarchar(1000) --要搜索的字符串
as
if @str is null returndeclare @s Nvarchar(4000)
create table #t(表名 sysname,字段名 sysname)declare tb cursor local for
select s='if exists(select 1 from ['+replace(b.name,']',']]')+'] where ['+a.name+'] like N''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0 and a.xtype in(175,239,99,35,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go
表一城市代码表结构
fcitycode fareacode
08JD001 08JD
08JD002 08JD
表二进销存表结构
fcitycode fstockdate
08JD001 '2005-01'
08JD001 '2005-02'
08JD002 '2005-01'
表三员工工资表结构
fcitycode femployeecode
08JD001 08JD001001
08JD001 08JD001002
08JD002 08JD002001
还有N张表中都有FCITYCODE这个字段,然后这个字段中都有08JD001这个值
我现在的问题就是想找出这个数据库中那个表中有‘08JD001'这个值,
并且一次性把这个值改为‘08SD001’,相应的FEMOPLYEECODE代码中的值‘08JD001001’,‘08JD001002’也能改为‘08SD001001’,‘08SD001002’
邹建老大的程序只是搜索出这个待搜索字段‘08JD001’,并没有告诉我如何把找到的所有‘08JD001’一次性的改为我想要的‘08SD001’,相应的员工代码‘08JD001001’等也改成‘08SD001001’想各位高手帮帮忙了.谢谢了
drop procedure [dbo].[p_replace]
goif exists(select * from sysobjects where id=object_id('#'))
drop table #
goset nocount on
go--创建存储过程
create proc p_replace
@colname Nvarchar(1000), --要搜索的字段
@rstr nvarchar(200) --要替换的内容
as
if @colname is null return
declare @s Nvarchar(4000)
select a.name col,b.name tb into # from syscolumns a join sysobjects b on a.id=b.id and a.name like '%'+@colname+'%'
declare cur cursor for
select * from #
open cur
declare @tbname nvarchar(20)
declare @columnname nvarchar(20)
fetch next from cur into @columnname,@tbname
while @@fetch_status=0
begin
set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+''''
--print(@s)
exec(@s)
fetch next from cur into @columnname,@tbname
end
close cur
deallocate cur
go--测试存储过程
exec p_replace N'adduser',N'hhhhhhhhhhhhh'
都没有改过来。
你能说清楚点吗?我试了还是不行啊
你的程序我改了一点
由 begin
set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+''''
--print(@s)
exec(@s)
fetch next from cur into @columnname,@tbname
end
改成
begin
set @s=N'update '+@tbname+N' set '+@columnname+'='''+@rstr+'''
where '+@columnname+'='''+@colname+''''
print(@s)
exec(@s)
fetch next from cur into @columnname,@tbname
end
加了一个where条件,基本可以,但是还有点问题,就是没有@rstr值的表也给出来了,只要有a.name 他就显示,但问题是有a.name字段并不一定有@str值,如何过滤掉这些值呢