server sql 根据列明获取表名_SQL Server 2008 R2——根据数据查找表名和字段名根据脏数据定位表和字段...

最新推荐文章于 2023-04-07 14:27:58 发布

皮肤科老徐医生

最新推荐文章于 2023-04-07 14:27:58 发布

阅读量334

点赞数

文章标签： server sql 根据列明获取表名

本文链接：https://blog.csdn.net/weixin_29272301/article/details/114469483

版权

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

按照“动态拼接字符串在'表名.字段名'中匹配查找”的最简易思路，我自己实现了一个。

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

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

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

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

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

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

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

1 ----------------------------------------------------

2 --网络代码有风险，复制粘贴要谨慎！

3 --by wlsandwhos@foxmail.com

5 ----------------------------------------------------

6 --使用tempdb库做本次测试

7 USE tempdb

8 GO

10 ----------------------------------------------------

11 IF OBJECT_ID (N't_Table1', N'U') IS NOT NULL

12 DROP TABLE t_Table1;

13 GO

15 IF OBJECT_ID (N't_Table2', N'U') IS NOT NULL

16 DROP TABLE t_Table2;

17 GO

19 IF OBJECT_ID (N't_Table3', N'U') IS NOT NULL

20 DROP TABLE t_Table3;

21 GO

23 IF OBJECT_ID (N't_Table4', N'U') IS NOT NULL

24 DROP TABLE t_Table4;

25 GO

27 IF OBJECT_ID (N't_Table5', N'U') IS NOT NULL

28 DROP TABLE t_Table5;

29 GO

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

45 CREATE TABLE t_Table2

46 (

47 t2f1 FLOAT PRIMARY KEY,

48 t2f2 REAL)

49 GO

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

62 CREATE TABLE t_Table4

63 (

64 t4f1 CHAR PRIMARY KEY,

65 t4f2 VARCHAR(10),

66 t4f3 TEXT

67 )

68 GO

70 CREATE TABLE t_Table5

71 (

72 t5f1 NCHAR(10) PRIMARY KEY,

73 t5f2 NVARCHAR(10),

74 t5f3 NTEXT

75 )

76 GO

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

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

皮肤科老徐医生

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
server sql 根据列明获取表名_SQL Server 2008 R2——根据数据查找表名和字段名根据脏数据定位表和字段...

在不知道表名字段名的情况下，在数据库里查找一个数据在什么地方。按照“动态拼接字符串在'表名.字段名'中匹配查找”的最简易思路，我自己实现了一个。万一我以后也要用呢？动动手总是没坏处的。=======================================================================下面的SQL代码中，我在tempdb里创建了几个有各种类型字段的表并且插入...
复制链接

扫一扫

server sql 根据列明获取表名_SQL Server 2008 R2——根据数据查找表名和字段名 根据脏数据定位表和字段...

server sql 根据列明获取表名_SQL Server 2008 R2——根据数据查找表名和字段名根据脏数据定位表和字段...