国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > Sqlserver > SQL SERVER根据值查找所在的表和字段

SQL SERVER根据值查找所在的表和字段

来源:程序员人生   发布时间:2014-05-06 06:21:49 阅读次数:3069次
需求
已知一个数据(字符串,数值等)是从一个数据库中查询出的,但是数据库表以及表结构不知。需要通过该值查找出所保存的表名以及字段名,目标暂定为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,会出错。经修正后的语句如下,以后可以根据需求自己修改: 
 
DECLARE @WHAT VARCHAR(800) 
SET @WHAT='178' --要搜索的字符串 

DECLARE @SQL VARCHAR(8000) 
 
DECLARE TABLECURSOR CURSOR LOCAL FOR 
SELECT SQL='IF EXISTS ( SELECT 1 FROM ['+ S.NAME + '].['+O.NAME+'] WHERE ['+C.NAME+'] LIKE ''%'+@WHAT+'%'' ) PRINT ''所在的表及字段:['+O.NAME+'].['+C.NAME+']''' 
FROM SYSCOLUMNS C JOIN SYS.OBJECTS O ON C.ID=O.OBJECT_ID 
JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID 
WHERE S.NAME != 'SYS' 
 
-- 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
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生