oracle在所有表中查值,oracle根据字段值查询所有表及其字段

declare

CURSOR cur_query IS

select table_name, column_name, data_type from user_tab_columns;

a number;

sql_hard varchar2(2000);

vv number;

begin

for rec1 in cur_query loop

a:=0;

if rec1.data_type ='NUMBER' THEN

a := 1;

end if;

if a>0 then

sql_hard := '';

sql_hard := 'SELECT count(*) FROM '|| rec1.table_name ||' where '

||rec1.column_name|| '=38000 ';

--dbms_output.put_line(sql_hard);

execute immediate sql_hard INTO vv;

IF vv > 0 THEN

dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);

end if;

END IF;

end loop;

end;

declare

CURSOR cur_query IS

select table_name, column_name, data_type from user_tab_columns;

a number;

sql_hard varchar2(2000);

vv number;

begin

for rec1 in cur_query loop

a:=0;

if rec1.data_type ='VARCHAR2' or rec1.data_type='CHAR' THEN

a := 1;

end if;

if a>0 then

sql_hard := '';

sql_hard := 'SELECT count(*) FROM '|| rec1.table_name ||' where '

||rec1.column_name|| ' like''00110000%''';

--dbms_output.put_line(sql_hard);

execute immediate sql_hard INTO vv;

IF vv > 0 THEN

dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);

end if;

END IF;

end loop;

end;

sql server 在数据库中找到包含指定字符串的数据表名及相应记录

declare @key varchar(30)

set @key = 'ICG' --替换为要查找的字符串

DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)

declare @sql VARCHAR(2000)

declare @tsql varchar(8000)

DECLARE tabCursor CURSOR FOR

SELECT name From sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'

OPEN tabCursor

FETCH NEXT FROM tabCursor INTO @tabName

WHILE @@fetch_status = 0

BEGIN

set @tsql = ''

DECLARE colCursor CURSOR FOR Select Name FROM SysColumns Where id=Object_Id(@tabName) and xtype=167

OPEN colCursor

FETCH NEXT FROM colCursor INTO @colName

WHILE @@fetch_status = 0

BEGIN

SET @sql = 'if(exists(select * from ' + @tabName + ' where '

SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from '

set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''

+ @tabName + ''' as TableName end'

set @tsql = @tsql + @sql + ';'

FETCH NEXT FROM colCursor INTO @colName

END

exec(@tsql)

CLOSE colCursor

DEALLOCATE colCursor

FETCH NEXT FROM tabCursor INTO @tabName

END

CLOSE tabCursor

DEALLOCATE tabCursor