SQL SERVER根據(jù)值查找所在的表和字段

需求
已知一個(gè)數(shù)據(jù)（字符串，數(shù)值等）是從一個(gè)數(shù)據(jù)庫中查詢出的，但是數(shù)據(jù)庫表以及表結(jié)構(gòu)不知。需要通過該值查找出所保存的表名以及字段名，目標(biāo)暫定為SqlServer。Oracle思路明白也可作出。

該問題來源于一個(gè)同事提出，數(shù)據(jù)交換時(shí)，由于現(xiàn)有系統(tǒng)是由第三方軟件提供商設(shè)計(jì)，數(shù)據(jù)庫不提供，在此對這種行為表示鄙視！

本文并非是用來解決該問題的解決方案，畢竟不是常規(guī)的方法，最好是由客戶出面協(xié)商解決。本文只是一種方法的研究，前期階段可以暫時(shí)如此。

分析
如果已知表和字段結(jié)構(gòu)，查找已知字段的固定或不固定值，是很簡單的，用簡單sql語句查詢即可，如 ： SELECT FIELD FROM TABLE WHERE FIELD LIKE '%STRING%';

現(xiàn)在只知道STRING的值，需要知道FIELD 和 TABLE，可以通過數(shù)據(jù)庫中的數(shù)據(jù)字典查出。

數(shù)據(jù)字典的查找方法見本人另一篇日志 SqlServer2005數(shù)據(jù)庫字典

通過遍歷輪詢數(shù)據(jù)庫中所有的表的所有符合字段，查出該表的該字段是否存在數(shù)據(jù)值。

即通過數(shù)據(jù)庫中的數(shù)據(jù)字典，構(gòu)造輪詢查找所有表所有字段的語句，得出是否存在對應(yīng)數(shù)值。最后形式為很多個(gè)SELECT 語句，都進(jìn)行查詢。

解決方法
思路明確后，就能往下進(jìn)行了，通過網(wǎng)絡(luò)搜索，居然也有人遇到如此問題，而且對SqlServer的構(gòu)造查詢語句查詢的TSQL也已經(jīng)有了，對此申明：本人不是拿來主義者，只是思路剛好一致了，網(wǎng)絡(luò)真是個(gè)神奇的地方。

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

-- 刪除游標(biāo)引用
DEALLOCATE TableCursor

解釋如下：先通過對數(shù)據(jù)字典查詢，構(gòu)造出SQL查詢語句列表，然后用游標(biāo)對該列表逐一執(zhí)行查詢語句。經(jīng)測試成功，但有些地方待改進(jìn)。

經(jīng)測試，上述代碼能夠查詢系統(tǒng)中所有表的所有字段，但是登陸用戶不同，結(jié)果有差異。

原因?yàn)闆]有限定查詢的Schema，對SYS和DBO查詢沒問題，但SYS架構(gòu)一般不需要，其他需要的架構(gòu)又直接去除了架構(gòu)名稱。所以會有問題，比如DBO2.TABLE1，上述查詢語句仍然為SELECT ... FROM TABLE1，會出錯(cuò)。經(jīng)修正后的語句如下，以后可以根據(jù)需求自己修改： 
 
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 
 
-- 刪除游標(biāo)引用 
DEALLOCATE TABLECURSOR