找字段中的值简单啊。 例如表abc中有列ident表示身份证号,那么查询123456789012345678的身份证,sql如下: select * from abc where ident='123456789012345678';
以下是sql server 的,你只需要转换一下就行了------------------------------------------------------------------Create or replace procedure Full_Search (@string varchar(100)) as begin declare @tbname varchar(100) declare tbroy cursor for select name from sysobjects where xtype='u' --第一个游标遍历所有的表open tbroy fetch next from tbroy into @tbname while @@fetch_status=0 begin declare @colname varchar(100) declare colroy cursor for select name from syscolumns where id=object_id(@tbname) and xtype in ( select xtype from systypes where name in ('varchar','nvarchar','char','nchar') --数据类型为字符型的字段 ) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段open colroy fetch next from colroy into @colname while @@fetch_status=0 begin declare @sql nvarchar(4000),@j int select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%''' exec sp_executesql immediate @sql,N'@i int output',@i=@j output --输出满足条件表的记录数 if @j>0 begin declare @v varchar(8000) set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%''' exec(@v) end fetch next from colroy into @colname end close colroy deallocate colroyfetch next from tbroy into @tbname end close tbroy deallocate tbroy endexec Full_Search '市场'drop proc Full_Search
以下是sql server 的,你只需要转换一下就行了 ------------------------------------------------------------------ Create or replace procedure Full_Search (@string varchar(100)) as begin declare @tbname varchar(100) declare tbroy cursor for select name from sysobjects where xtype='u' --第一个游标遍历所有的表 open tbroy fetch next from tbroy into @tbname while @@fetch_status=0 begin declare @colname varchar(100) declare colroy cursor for select name from syscolumns where id=object_id(@tbname) and xtype in ( select xtype from systypes where name in ('varchar','nvarchar','char','nchar') --数据类型为字符型的字段 ) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段 open colroy fetch next from colroy into @colname while @@fetch_status=0 begin declare @sql nvarchar(4000),@j int select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%''' exec sp_executesql immediate @sql,N'@i int output',@i=@j output --输出满足条件表的记录数 if @j>0 begin declare @v varchar(8000) set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%''' exec(@v) end fetch next from colroy into @colname end close colroy deallocate colroy fetch next from tbroy into @tbname end close tbroy deallocate tbroy end exec Full_Search '市场' drop proc Full_Search
1.如果‘张三’全在 user_tab_columns 字段中,则在此库中可找出所有包含此字段的表: select tname from col where cname='user_tab_columns'; 或:select tname from sys.col where cname='user_tab_columns'; 2.如果字段名不一致,则用只好用手工来做。
注意:'user_tab_columns'中引号间括住的字符一定要大写,如'CUSTOMER'。
大体是这样,自己按需求修改吧 PROCEDURE cx IS sqlyj varchar2(1000); str number(22); mycursor INTEGER; ignore INTEGER; mytab varchar2(100); v_tab varchar(100); v_col varchar2(100); cursor a is select table_name,column_name,'select count(*) from '||table_name||' where '||column_name||'=''张三''' from user_tab_columns; begin open a; fetch a into v_tab,v_col,sqlyj; while a %found loop mycursor := dbms_sql.open_cursor; dbms_sql.parse(mycursor,sqlyj,1); DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,22); ignore := dbms_sql.execute(mycursor); if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then DBMS_SQL.COLUMN_VALUE(mycursor,1,str); if str>0 then null; /* 执行你需要的更新,表名存在v_tab中,列名存在v_col中 */ end if; end if; dbms_sql.close_cursor(mycursor); fetch a into v_tab,v_col,sqlyj; end loop; end;
如果是字段的名字
select table_name from user_tab_columns where column_name='张三'
如果是值,好象一个sql就搞不定了,
可以写个过程实现
例如表abc中有列ident表示身份证号,那么查询123456789012345678的身份证,sql如下:
select * from abc where ident='123456789012345678';
(@string varchar(100))
as
begin
declare @tbname varchar(100)
declare tbroy cursor for select name from sysobjects where xtype='u' --第一个游标遍历所有的表open tbroy fetch next from tbroy into @tbname while @@fetch_status=0
begin
declare @colname varchar(100)
declare colroy cursor for
select name from syscolumns
where id=object_id(@tbname) and xtype in ( select xtype from systypes where name in ('varchar','nvarchar','char','nchar')
--数据类型为字符型的字段 ) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段open colroy fetch next from colroy into
@colname while @@fetch_status=0
begin
declare @sql nvarchar(4000),@j int
select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec sp_executesql immediate @sql,N'@i int output',@i=@j output --输出满足条件表的记录数 if @j>0
begin declare @v varchar(8000)
set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec(@v) end fetch next from colroy into @colname end
close colroy
deallocate colroyfetch next from tbroy into @tbname end close tbroy deallocate tbroy
endexec Full_Search '市场'drop proc Full_Search
以下是sql server 的,你只需要转换一下就行了 ------------------------------------------------------------------ Create or replace procedure Full_Search
(@string varchar(100))
as
begin
declare @tbname varchar(100)
declare tbroy cursor for select name from sysobjects where xtype='u' --第一个游标遍历所有的表 open tbroy fetch next from tbroy into @tbname while @@fetch_status=0
begin
declare @colname varchar(100)
declare colroy cursor for
select name from syscolumns
where id=object_id(@tbname) and xtype in ( select xtype from systypes where name in ('varchar','nvarchar','char','nchar')
--数据类型为字符型的字段 ) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段 open colroy fetch next from colroy into
@colname while @@fetch_status=0
begin
declare @sql nvarchar(4000),@j int
select @sql='select @i=count(1) from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec sp_executesql immediate @sql,N'@i int output',@i=@j output --输出满足条件表的记录数 if @j>0
begin declare @v varchar(8000)
set @v='select distinct '+quotename(@colname)+' from ' +quotename(@tbname) +' where '+ quotename(@colname)+' like '+'''%'+@string+'%'''
exec(@v) end fetch next from colroy into @colname end
close colroy
deallocate colroy fetch next from tbroy into @tbname end close tbroy deallocate tbroy
end exec Full_Search '市场' drop proc Full_Search
select tname from col where cname='user_tab_columns';
或:select tname from sys.col where cname='user_tab_columns';
2.如果字段名不一致,则用只好用手工来做。
PROCEDURE cx IS
sqlyj varchar2(1000);
str number(22);
mycursor INTEGER;
ignore INTEGER;
mytab varchar2(100);
v_tab varchar(100);
v_col varchar2(100);
cursor a is select table_name,column_name,'select count(*) from '||table_name||' where '||column_name||'=''张三''' from user_tab_columns;
begin
open a;
fetch a into v_tab,v_col,sqlyj;
while a %found loop
mycursor := dbms_sql.open_cursor;
dbms_sql.parse(mycursor,sqlyj,1);
DBMS_SQL.DEFINE_COLUMN(mycursor,1,str,22);
ignore := dbms_sql.execute(mycursor);
if DBMS_SQL.FETCH_ROWS(mycursor)<> 0 then
DBMS_SQL.COLUMN_VALUE(mycursor,1,str);
if str>0 then
null;
/*
执行你需要的更新,表名存在v_tab中,列名存在v_col中
*/
end if;
end if;
dbms_sql.close_cursor(mycursor);
fetch a into v_tab,v_col,sqlyj;
end loop;
end;