SQL Server 2008 R2——根据数据查找表名和字段名根据脏数据定位表和字段

请通过右侧公告中的“联系邮箱（wlsandwho@foxmail.com）”联系我

勿用于学术性引用。

勿用于商业出版、商业印刷、商业引用以及其他商业用途。　　　　　　　　　　　　　　　　

本文不定期修正完善。

本文链接：http://www.cnblogs.com/wlsandwho/p/4887269.html

耻辱墙：http://www.cnblogs.com/wlsandwho/p/4206472.html

=======================================================================

企鹅群真是个无尽的题材来源。

某群友要在不知道表名字段名的情况下，在数据库里查找一个数据在什么地方。

如图。

当时大家都在忙，没来得及响应。于是有了下面的一段聊天。

=======================================================================

虽然告诉了TA应该怎么做，可后来该群友还是将每个表都导出成文本格式，挨个搜索的——据说他不会写SQL代码。

想到这个问题还是很有意思的，按照“动态拼接字符串在'表名.字段名'中匹配查找”的最简易思路，我自己实现了一个。

万一我以后也要用呢？动动手总是没坏处的。

=======================================================================

下面的SQL代码中，我在tempdb里创建了几个有各种类型字段的表并且插入了几个简单数据来做测试。

实际使用时应当将所有的tempdb替换成目标数据库，并且给@StrLookingfor赋上要查找的值。

本SQL代码没有做其他的测试，不能保证正确有效。网络代码有风险，复制粘贴要谨慎！

另外比较奇怪的是，感觉结果不是那么的完整，好奇怪的样子，有空再改改吧。

=======================================================================

  1 ----------------------------------------------------
  2 --网络代码有风险，复制粘贴要谨慎！
  3 --by wlsandwhos@foxmail.com
  4 
  5 ----------------------------------------------------
  6 --使用tempdb库做本次测试
  7 USE tempdb
  8 GO
  9 
 10 ----------------------------------------------------
 11 IF OBJECT_ID (N't_Table1', N'U') IS NOT NULL
 12 DROP TABLE t_Table1;
 13 GO
 14 
 15 IF OBJECT_ID (N't_Table2', N'U') IS NOT NULL
 16 DROP TABLE t_Table2;
 17 GO
 18 
 19 IF OBJECT_ID (N't_Table3', N'U') IS NOT NULL
 20 DROP TABLE t_Table3;
 21 GO
 22 
 23 IF OBJECT_ID (N't_Table4', N'U') IS NOT NULL
 24 DROP TABLE t_Table4;
 25 GO
 26 
 27 IF OBJECT_ID (N't_Table5', N'U') IS NOT NULL
 28 DROP TABLE t_Table5;
 29 GO
 30 
 31 ----------------------------------------------------
 32 CREATE TABLE t_Table1
 33 (
 34 t1f1 BIGINT PRIMARY KEY,
 35 t1f2 NUMERIC,
 36 t1f3 BIT,
 37 t1f4 SMALLINT,
 38 t1f5 DECIMAL,
 39 t1f6 SMALLMONEY,
 40 t1f7 INT,
 41 t1f8 TINYINT,
 42 t1f9 MONEY)
 43 GO
 44 
 45 CREATE TABLE t_Table2
 46 (
 47 t2f1 FLOAT PRIMARY KEY,
 48 t2f2 REAL)
 49 GO
 50 
 51 CREATE TABLE t_Table3
 52 (
 53 t3f1 DATE PRIMARY KEY,
 54 t3f2 DATETIMEOFFSET,
 55 t3f3 DATETIME2,
 56 t3f4 SMALLDATETIME,
 57 t3f5 DATETIME,
 58 t3f6 TIME
 59 )
 60 GO
 61 
 62 CREATE TABLE t_Table4
 63 (
 64 t4f1 CHAR PRIMARY KEY,
 65 t4f2 VARCHAR(10),
 66 t4f3 TEXT
 67 )
 68 GO
 69 
 70 CREATE TABLE t_Table5
 71 (
 72 t5f1 NCHAR(10) PRIMARY KEY,
 73 t5f2 NVARCHAR(10),
 74 t5f3 NTEXT
 75 )
 76 GO
 77 
 78 ----------------------------------------------------
 79 INSERT INTO t_Table1 VALUES(666,1666,1,2333,23666,233.666,23333,66,2336666.233)
 80 INSERT INTO t_Table1 VALUES(667,6266,0,2133,21666,213.666,21333,61,2136666.233)
 81 INSERT INTO t_Table1 VALUES(668,6636,1,2323,23266,232.666,23233,62,2326666.233)
 82 INSERT INTO t_Table1 VALUES(669,6646,0,2333,23636,233.366,23333,63,2333666.233)
 83 INSERT INTO t_Table1 VALUES(670,6665,1,2343,23664,233.646,23334,64,2336466.233)
 84 INSERT INTO t_Table1 VALUES(671,6666,0,2533,23656,233.665,23353,65,2336656.233)
 85 INSERT INTO t_Table1 VALUES(672,6676,0,2363,23666,233.666,23633,66,2336666.233)
 86 INSERT INTO t_Table1 VALUES(673,6866,1,2337,27666,233.766,27333,67,2336666.733)
 87 INSERT INTO t_Table1 VALUES(674,6696,0,2383,23866,238.666,23833,68,2336666.283)
 88 INSERT INTO t_Table1 VALUES(675,6660,1,2933,23696,293.666,23393,69,2336666.239)
 89 INSERT INTO t_Table1 VALUES(676,6616,0,2303,23660,230.666,23330,60,2336666.203)
 90 GO
 91 
 92 INSERT INTO t_Table2 VALUES(233.666,2336666.233)
 93 INSERT INTO t_Table2 VALUES(213.666,2136666.233)
 94 INSERT INTO t_Table2 VALUES(232.666,2326666.233)
 95 INSERT INTO t_Table2 VALUES(233.366,2333666.233)
 96 INSERT INTO t_Table2 VALUES(233.646,2336466.233)
 97 INSERT INTO t_Table2 VALUES(233.665,2336656.233)
 98 INSERT INTO t_Table2 VALUES(233.166,2336666.233)
 99 INSERT INTO t_Table2 VALUES(233.766,2336666.733)
100 INSERT INTO t_Table2 VALUES(238.666,2336666.283)
101 INSERT INTO t_Table2 VALUES(293.666,2336666.239)
102 INSERT INTO t_Table2 VALUES(230.666,2336666.203)
103 GO
104 
105 INSERT INTO t_Table3 VALUES('0001-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
106 INSERT INTO t_Table3 VALUES('1001-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
107 INSERT INTO t_Table3 VALUES('0101-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
108 INSERT INTO t_Table3 VALUES('0011-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
109 INSERT INTO t_Table3 VALUES('0001-11-11','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
110 INSERT INTO t_Table3 VALUES('2001-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
111 INSERT INTO t_Table3 VALUES('0201-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
112 INSERT INTO t_Table3 VALUES('0021-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234666')
113 INSERT INTO t_Table3 VALUES('0001-11-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234666')
114 INSERT INTO t_Table3 VALUES('0001-01-21','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
115 INSERT INTO t_Table3 VALUES('2201-01-01','00:00:00.123','2007-05-08 12:35:29.123','2007-05-08 12:35:00','1900-01-01 00:00:00','12:35:29.1234567')
116 GO
117 
118 INSERT INTO t_Table4 VALUES('a','aa','aaa')
119 INSERT INTO t_Table4 VALUES('b','bb','bbb')
120 INSERT INTO t_Table4 VALUES('c','cc','ccc')
121 INSERT INTO t_Table4 VALUES('d','dd','ddd')
122 INSERT INTO t_Table4 VALUES('e','ee','eee')
123 INSERT INTO t_Table4 VALUES('f','ff','fff')
124 INSERT INTO t_Table4 VALUES('g','gg','666')
125 INSERT INTO t_Table4 VALUES('h','hh','hhh')
126 INSERT INTO t_Table4 VALUES('i','ii','iii')
127 INSERT INTO t_Table4 VALUES('j','jj','jjj')
128 INSERT INTO t_Table4 VALUES('k','kk','kkk')
129 GO
130 
131 INSERT INTO t_Table5 VALUES('a','aa','aaa')
132 INSERT INTO t_Table5 VALUES('b','bb','bbb')
133 INSERT INTO t_Table5 VALUES('c','cc','ccc')
134 INSERT INTO t_Table5 VALUES('d','dd','ddd')
135 INSERT INTO t_Table5 VALUES('e','ee','eee')
136 INSERT INTO t_Table5 VALUES('f','ff','fff')
137 INSERT INTO t_Table5 VALUES('g','gg','ggg')
138 INSERT INTO t_Table5 VALUES('h','hh','666')
139 INSERT INTO t_Table5 VALUES('i','ii','iii')
140 INSERT INTO t_Table5 VALUES('j','jj','jjj')
141 INSERT INTO t_Table5 VALUES('k','kk','kkk')
142 GO
143 
144 ----------------------------------------------------
145 SELECT * FROM t_Table1
146 SELECT * FROM t_Table2
147 SELECT * FROM t_Table3
148 SELECT * FROM t_Table4
149 SELECT * FROM t_Table5
150 GO
151 
152 ----------------------------------------------------
153 --临时表，用于存储所有的表名和字段名
154 IF OBJECT_ID (N'#t_TNCN', N'U') IS NOT NULL
155 DROP TABLE #t_TNCN;
156 GO
157 
158 CREATE TABLE #t_TNCN
159 (
160 --id INTEGER IDENTITY(1,1) PRIMARY KEY,
161 tn NVARCHAR(32),
162 cn NVARCHAR(32)
163 )
164 GO
165 
166 --临时表，用于存储最终的结果
167 IF OBJECT_ID (N'#t_TNCN2', N'U') IS NOT NULL
168 DROP TABLE #t_TNCN2;
169 GO
170 
171 CREATE TABLE #t_TNCN2
172 (
173 --id INTEGER IDENTITY(1,1) PRIMARY KEY,
174 tn NVARCHAR(32),
175 cn NVARCHAR(32)
176 )
177 GO
178 
179 ----------------------------------------------------
180 --获取tempdb数据库的所有表名和字段名
181 INSERT INTO #t_TNCN
182 SELECT  TN.name AS tn ,
183         CN.name AS cn
184 FROM    tempdb..SysObjects AS TN
185         CROSS APPLY ( SELECT    Name
186                       FROM      SysColumns
187                       WHERE     id = OBJECT_ID(TN.name)
188                     ) AS CN
189 WHERE   TN.XType = 'U'
190         AND Tn.name NOT LIKE '#%'
191         --ORDER BY tn
192 GO
193 --SELECT * FROM #t_TNCN
194 --GO
195 
196 ----------------------------------------------------
197 --用游标进行动态拼接查询，并把结果放入第二个临时表
198 DECLARE @StrLookingfor NVARCHAR(32)
199 SET @StrLookingfor='233'--要查询的数据
200 PRINT @StrLookingfor
201 
202 DECLARE @StrSql NVARCHAR(256)
203 DECLARE @tblname NVARCHAR(32)
204 DECLARE @colname NVARCHAR(32)
205 
206 DECLARE myFilterCursor CURSOR FOR 
207 SELECT tn,cn FROM #t_TNCN
208 OPEN myFilterCursor
209 FETCH NEXT FROM myFilterCursor INTO @tblname,@colname
210 WHILE @@FETCH_STATUS=0
211 BEGIN 
212     SET @StrSql='insert into #t_TNCN2(tn,cn) select  '''+@tblname+''','''+@colname+''' from '+@tblname+' where CONVERT(NVARCHAR(32),'+@colname+') LIKE ''%'+@StrLookingfor+'%'''
213     EXEC(@StrSql)
214     FETCH NEXT FROM myFilterCursor INTO @tblname,@colname
215 END
216 CLOSE myFilterCursor
217 DEALLOCATE myFilterCursor
218 GO
219 
220 ----------------------------------------------------
221 --结果
222 SELECT tn,cn FROM #t_TNCN2
223 GO

=======================================================================

细节效率什么的放过我吧。

非专业SQL，不求高效，但求能跑。

发表于 2015-10-20 08:53 王林森阅读(4406) 评论(0) 编辑收藏举报

刷新页面返回顶部

SQL Server 2008 R2——根据数据查找表名和字段名 根据脏数据定位表和字段

公告

SQL Server 2008 R2——根据数据查找表名和字段名根据脏数据定位表和字段