- SQL Server 中根据字段值查询其所在的表、字段
假如字段值=123456，根据其查询表名和字段名DECLARE @what varchar(800) SET @what='123456' --要搜索的字符串 DECLARE @sql varchar(8000) 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 -- 175=char 56=int 可以查 select * from sys.types WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 ) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sql WHILE @@FETCH_STATUS=0 BEGIN EXEC( @sql ) FETCH NEXT FROM TableCursor INTO @sql END CLOSE TableCursor -- 删除游标引用 DEALLOCATE TableCursor
- Oracle 中根据字段值查询其所在的表、字段
假如字段值=123456，根据其查询表名和字段名方法一：--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''123456''';--字段值 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;方法二：
--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 || '=123456';--字段值 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;