=================================版权声明=================================
版权声明：原创文章 谢绝转载
请通过右侧公告中的“联系邮箱（[email protected]）”联系我
勿用于学术性引用。
勿用于商业出版、商业印刷、商业引用以及其他商业用途。
本文不定期修正完善。
本文链接：http://www.cnblogs.com/wlsandwho/p/4887269.html
耻辱墙：http://www.cnblogs.com/wlsandwho/p/4206472.html
=======================================================================
企鹅群真是个无尽的题材来源。
某群友要在不知道表名字段名的情况下，在数据库里查找一个数据在什么地方。
如图。
当时大家都在忙，没来得及响应。于是有了下面的一段聊天。
=======================================================================
虽然告诉了TA应该怎么做，可后来该群友还是将每个表都导出成文本格式，挨个搜索的——据说他不会写SQL代码。
想到这个问题还是很有意思的，按照“动态拼接字符串在'表名.字段名'中匹配查找”的最简易思路，我自己实现了一个。
万一我以后也要用呢？动动手总是没坏处的。
=======================================================================
下面的SQL代码中，我在tempdb里创建了几个有各种类型字段的表并且插入了几个简单数据来做测试。
实际使用时应当将所有的tempdb替换成目标数据库，[email protected]
本SQL代码没有做其他的测试，不能保证正确有效。网络代码有风险，复制粘贴要谨慎！
另外比较奇怪的是，感觉结果不是那么的完整，好奇怪的样子，有空再改改吧。
=======================================================================
1 ---------------------------------------------------- 2 --网络代码有风险，复制粘贴要谨慎！ 3 --by [email protected] 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 GO104 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 GO117 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 GO130 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 GO143 144 ----------------------------------------------------145 SELECT * FROM t_Table1146 SELECT * FROM t_Table2147 SELECT * FROM t_Table3148 SELECT * FROM t_Table4149 SELECT * FROM t_Table5150 GO151 152 ----------------------------------------------------153 --临时表，用于存储所有的表名和字段名154 IF OBJECT_ID (N'#t_TNCN', N'U') IS NOT NULL155 DROP TABLE #t_TNCN;156 GO157 158 CREATE TABLE #t_TNCN159 (160 --id INTEGER IDENTITY(1,1) PRIMARY KEY,161 tn NVARCHAR(32),162 cn NVARCHAR(32)163 )164 GO165 166 --临时表，用于存储最终的结果167 IF OBJECT_ID (N'#t_TNCN2', N'U') IS NOT NULL168 DROP TABLE #t_TNCN2;169 GO170 171 CREATE TABLE #t_TNCN2172 (173 --id INTEGER IDENTITY(1,1) PRIMARY KEY,174 tn NVARCHAR(32),175 cn NVARCHAR(32)176 )177 GO178 179 ----------------------------------------------------180 --获取tempdb数据库的所有表名和字段名181 INSERT INTO #t_TNCN182 SELECT TN.name AS tn ,183 CN.name AS cn184 FROM tempdb..SysObjects AS TN185 CROSS APPLY ( SELECT Name186 FROM SysColumns187 WHERE id = OBJECT_ID(TN.name)188 ) AS CN189 WHERE TN.XType = 'U'190 AND Tn.name NOT LIKE '#%'191 --ORDER BY tn192 GO193 --SELECT * FROM #t_TNCN194 --GO195 196 ----------------------------------------------------197 --用游标进行动态拼接查询，并把结果放入第二个临时表198 DECLARE @StrLookingfor NVARCHAR(32)199 SET @StrLookingfor='233'--要查询的数据200 PRINT @StrLookingfor201 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_TNCN208 OPEN myFilterCursor209 FETCH NEXT FROM myFilterCursor INTO @tblname,@colname210 WHILE @@FETCH_STATUS=0211 BEGIN 212 SET @StrSql='insert into #t_TNCN2(tn,cn) select '''[email protected]+''','''[email protected]+''' from '[email protected]+' where CONVERT(NVARCHAR(32),'[email protected]+') LIKE ''%'[email protected]+'%'''213 EXEC(@StrSql)214 FETCH NEXT FROM myFilterCursor INTO @tblname,@colname215 END216 CLOSE myFilterCursor217 DEALLOCATE myFilterCursor218 GO219 220 ----------------------------------------------------221 --结果222 SELECT tn,cn FROM #t_TNCN2223 GO
=======================================================================
细节效率什么的放过我吧。
非专业SQL，不求高效，但求能跑。