如何根据值查找所在的表和字段

最新推荐文章于 2021-11-27 22:59:45 发布

weixin_33919941

最新推荐文章于 2021-11-27 22:59:45 发布

阅读量367

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/pengyq/archive/2010/02/09/1666626.html

版权

需求

已知一个数据（字符串，数值等）是从一个数据库中查询出的，但是数据库表以及表结构不知。需要通过该值查找出所保存的表名以及字段名，目标暂定为SqlServer。Oracle思路明白也可作出。

该问题来源于一个同事提出，数据交换时，由于现有系统是由第三方软件提供商设计，数据库不提供，在此对这种行为表示鄙视！

本文并非是用来解决该问题的解决方案，毕竟不是常规的方法，最好是由客户出面协商解决。本文只是一种方法的研究，前期阶段可以暂时如此。

分析

如果已知表和字段结构，查找已知字段的固定或不固定值，是很简单的，用简单sql语句查询即可，如： SELECT FIELD FROM TABLE WHERE FIELD LIKE '%STRING%';

现在只知道STRING的值，需要知道FIELD 和 TABLE，可以通过数据库中的数据字典查出。

数据字典的查找方法见本人另一篇日志 SqlServer2005数据库字典

通过遍历轮询数据库中所有的表的所有符合字段，查出该表的该字段是否存在数据值。

即通过数据库中的数据字典，构造轮询查找所有表所有字段的语句，得出是否存在对应数值。最后形式为很多个SELECT 语句，都进行查询。

解决方法

思路明确后，就能往下进行了，通过网络搜索，居然也有人遇到如此问题，而且对SqlServer的构造查询语句查询的TSQL也已经有了，对此申明：本人不是拿来主义者，只是思路刚好一致了，网络真是个神奇的地方。

源码

SQL代码

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

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

解释如下：先通过对数据字典查询，构造出SQL查询语句列表，然后用游标对该列表逐一执行查询语句。经测试成功，但有些地方待改进。

经测试，上述代码能够查询系统中所有表的所有字段，但是登陆用户不同，结果有差异。

原因为没有限定查询的Schema，对SYS和DBO查询没问题，但SYS架构一般不需要，其他需要的架构又直接去除了架构名称。所以会有问题，比如DBO2.TABLE1，上述查询语句仍然为SELECT ... FROM TABLE1，会出错。经修正后的语句如下，以后可以根据需求自己修改：

1
2 DECLARE @WHAT VARCHAR ( 800 )
3 SET @WHAT = ' 178 ' -- 要搜索的字符串
4
5 DECLARE @SQL VARCHAR ( 8000 )
6
7 DECLARE TABLECURSOR CURSOR LOCAL FOR
8 SELECT SQL = ' IF EXISTS ( SELECT 1 FROM [ ' + S.NAME + ' ].[ ' + O.NAME + ' ] WHERE [ ' + C.NAME + ' ] LIKE '' % ' + @WHAT + ' % '' ) PRINT '' 所在的表及字段：[ ' + O.NAME + ' ].[ ' + C.NAME + ' ] '''
9 FROM SYSCOLUMNS C JOIN SYS.OBJECTS O ON C.ID = O. OBJECT_ID
10 JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID
11 WHERE S.NAME != ' SYS '
12
13 -- 175=CHAR 56=INT 可以查 SELECT * FROM SYS.TYPES
14 WHERE O.XTYPE = ' U ' AND C.STATUS >= 0 AND C.XUSERTYPE IN ( 175 , 239 , 231 , 167 )
15
16 OPEN TABLECURSOR
17
18 FETCH NEXT FROM TABLECURSOR INTO @SQL
19 WHILE @@FETCH_STATUS = 0
20 BEGIN
21 EXEC ( @SQL )
22 FETCH NEXT FROM TABLECURSOR INTO @SQL
23 END
24
25 CLOSE TABLECURSOR
26
27 -- 删除游标引用
28 DEALLOCATE TABLECURSOR

转载于:https://www.cnblogs.com/pengyq/archive/2010/02/09/1666626.html