oracle plsql 根据值查询在某个表,某个字段和 sql server 中根据值查询在某个表某个字段-蒲公英云

oracle plsql 根据值查询在某个表,某个字段和 sql server 中根据值查询在某个表某个字段

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 ='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 tabCurso
转自:https://www.cnblogs.com/wuyifu/archive/2013/06/06/3120307.html  侵删