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

需求
已知一個數據（字符串，數值等）是從一個數據庫中查詢出的，但是數據庫表以及表結構不知。需要通過該值查找出所保存的表名以及字段名，目標暫定為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