Oracle根据字段值找到表名和列名

方法1：

--Oracle 根据字段值查询其所在的表、字段
DECLARE
CURSOR cur_query IS
  SELECT table_name, column_name, data_type FROM user_tab_columns;
  a NUMBER;
  sql_hard VARCHAR2();
  vv NUMBER;
BEGIN
  FOR rec1 IN cur_query LOOP
  a:=;
  IF rec1.data_type ='VARCHAR2' OR rec1.data_type='CHAR' THEN
  a := ;
  END IF;
  IF a> THEN
  sql_hard := '';
  sql_hard := 'SELECT count(*) FROM '|| rec1.table_name ||' where '
  ||rec1.column_name|| ' like''吴芳''';--字段值
  dbms_output.put_line(sql_hard);
  EXECUTE IMMEDIATE sql_hard INTO vv;
  IF vv >  THEN dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');
  END IF;
  END IF;
  END LOOP;
END;  

方法2：

DECLARE
CURSOR cur_query IS
  SELECT table_name, column_name, data_type FROM user_tab_columns;
  a NUMBER;
  sql_hard VARCHAR2();
  vv NUMBER;
BEGIN
  FOR rec1 IN cur_query LOOP
  a:=;
  IF rec1.data_type ='NUMBER' THEN
  a := ;
  END IF;
  IF a> THEN
  sql_hard := '';
  sql_hard := 'SELECT COUNT(*) FROM  '|| rec1.table_name ||' WHERE '
  ||rec1.column_name
  || '=123456';--字段值
  dbms_output.put_line(sql_hard);
  EXECUTE IMMEDIATE sql_hard INTO vv;
  IF vv >  THEN
   dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');
  END IF;
  END IF;
  END LOOP;
END;  

SQLserver中如何实现

DECLARE @what varchar()
SET @what='' --要搜索的字符串   

DECLARE @sql varchar()   

DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+']
WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''[字段值所在的表.字段]：['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id   

-- =char =int 可以查 select * from sys.types
WHERE o.xtype='U' AND c.status>= AND c.xusertype IN (, , ,  )
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=
BEGIN
    EXEC( @sql )
    FETCH NEXT FROM TableCursor INTO @sql
END
CLOSE TableCursor
-- 删除游标引用
DEALLOCATE TableCursor   

本位转载自：复制到这里只是怕连接失效哦！！！

http://blog.****.net/yangyi22/article/details/7555662