-
mssql 跨表 跨数据库查询技巧
2014年07月15日 安全技术 热度1036 查看评论
最近一直在研究前期泄漏出来的XX的数据库，上亿条数据，几十个数据库文件，每个数据库文件里有几百个表，查询一条数据，无异于大海捞针，可累坏我了 。几经折腾，整理出几条搜索技巧，记录下来。
1.获取所有数据库名 Select Name From Master..SysDatabases order By Name
2.获取所有表名 Select Name From SysObjects Where XType='U' order By Name
XType='U':表示所有用户表;
XType='S':表示所有系统表;
3.获取所有字段名 Select Name From SysColumns Where id=Object_Id('TableName')
4.模糊查询
select * from Group401 where QQnum like '123456%'
5.跨表联合查询
SELECT * FROM Group901 where qqnum like '1234%' UNION all
SELECT * FROM Group902 where qqnum like '1234%' UNION all
SELECT * FROM Group903 where qqnum like '1234%' UNION all
SELECT * FROM Group904 where qqnum like '1234%'
SELECT * FROM Group1 where qqnum like '123456%' UNION all
SELECT * FROM Group2 where qqnum like '123456%' UNION all
SELECT * FROM Group3 where qqnum like '123456%' UNION all
SELECT * FROM Group4 where qqnum like '123456%' UNION all
SELECT * FROM Group5 where qqnum like '123456%' UNION all
SELECT * FROM Group6 where qqnum like '123456%' UNION all
SELECT * FROM Group7 where qqnum like '123456%' UNION all
SELECT * FROM Group8 where qqnum like '123456%' UNION all
SELECT * FROM Group9 where qqnum like '123456%' UNION all
SELECT * FROM Group10 where qqnum like '123456%' UNION all
SELECT * FROM Group11 where qqnum like '123456%' UNION all
SELECT * FROM Group12 where qqnum like '123456%' UNION all
SELECT * FROM Group13 where qqnum like '123456%' UNION all
SELECT * FROM Group14 where qqnum like '123456%' UNION all
SELECT * FROM Group15 where qqnum like '123456%' UNION all
SELECT * FROM Group16 where qqnum like '123456%' UNION all
SELECT * FROM Group17 where qqnum like '123456%' UNION all
SELECT * FROM Group18 where qqnum like '123456%' UNION all
SELECT * FROM Group19 where qqnum like '123456%' UNION all
SELECT * FROM Group20 where qqnum like '123456%' UNION all
SELECT * FROM Group21 where qqnum like '123456%' UNION all
SELECT * FROM Group22 where qqnum like '123456%' UNION all
SELECT * FROM Group23 where qqnum like '123456%' UNION all
SELECT * FROM Group24 where qqnum like '123456%' UNION all
SELECT * FROM Group25 where qqnum like '123456%' UNION all
SELECT * FROM Group26 where qqnum like '123456%' UNION all
SELECT * FROM Group27 where qqnum like '123456%' UNION all
SELECT * FROM Group28 where qqnum like '123456%' UNION all
SELECT * FROM Group29 where qqnum like '123456%' UNION all
SELECT * FROM Group30 where qqnum like '123456%' UNION all
SELECT * FROM Group31 where qqnum like '123456%' UNION all
SELECT * FROM Group32 where qqnum like '123456%' UNION all
SELECT * FROM Group33 where qqnum like '123456%' UNION all
SELECT * FROM Group34 where qqnum like '123456%' UNION all
SELECT * FROM Group35 where qqnum like '123456%' UNION all
SELECT * FROM Group36 where qqnum like '123456%' UNION all
SELECT * FROM Group37 where qqnum like '123456%' UNION all
SELECT * FROM Group38 where qqnum like '123456%' UNION all
SELECT * FROM Group39 where qqnum like '123456%' UNION all
SELECT * FROM Group40 where qqnum like '123456%' UNION all
SELECT * FROM Group41 where qqnum like '123456%' UNION all
SELECT * FROM Group42 where qqnum like '123456%' UNION all
SELECT * FROM Group43 where qqnum like '123456%' UNION all
SELECT * FROM Group44 where qqnum like '123456%' UNION all
SELECT * FROM Group45 where qqnum like '123456%' UNION all
SELECT * FROM Group46 where qqnum like '123456%' UNION all
SELECT * FROM Group47 where qqnum like '123456%' UNION all
SELECT * FROM Group48 where qqnum like '123456%' UNION all
SELECT * FROM Group49 where qqnum like '123456%' UNION all
SELECT * FROM Group50 where qqnum like '123456%' UNION all
SELECT * FROM Group51 where qqnum like '123456%' UNION all
SELECT * FROM Group52 where qqnum like '123456%' UNION all
SELECT * FROM Group53 where qqnum like '123456%' UNION all
SELECT * FROM Group54 where qqnum like '123456%' UNION all
SELECT * FROM Group55 where qqnum like '123456%' UNION all
SELECT * FROM Group56 where qqnum like '123456%' UNION all
SELECT * FROM Group57 where qqnum like '123456%' UNION all
SELECT * FROM Group58 where qqnum like '123456%' UNION all
SELECT * FROM Group59 where qqnum like '123456%' UNION all
SELECT * FROM Group60 where qqnum like '123456%' UNION all
SELECT * FROM Group61 where qqnum like '123456%' UNION all
SELECT * FROM Group62 where qqnum like '123456%' UNION all
SELECT * FROM Group63 where qqnum like '123456%' UNION all
SELECT * FROM Group64 where qqnum like '123456%' UNION all
SELECT * FROM Group65 where qqnum like '123456%' UNION all
SELECT * FROM Group66 where qqnum like '123456%' UNION all
SELECT * FROM Group67 where qqnum like '123456%' UNION all
SELECT * FROM Group68 where qqnum like '123456%' UNION all
SELECT * FROM Group69 where qqnum like '123456%' UNION all
SELECT * FROM Group70 where qqnum like '123456%' UNION all
SELECT * FROM Group71 where qqnum like '123456%' UNION all
SELECT * FROM Group72 where qqnum like '123456%' UNION all
SELECT * FROM Group73 where qqnum like '123456%' UNION all
SELECT * FROM Group74 where qqnum like '123456%' UNION all
SELECT * FROM Group75 where qqnum like '123456%' UNION all
SELECT * FROM Group76 where qqnum like '123456%' UNION all
SELECT * FROM Group77 where qqnum like '123456%' UNION all
SELECT * FROM Group78 where qqnum like '123456%' UNION all
SELECT * FROM Group79 where qqnum like '123456%' UNION all
SELECT * FROM Group80 where qqnum like '123456%' UNION all
SELECT * FROM Group81 where qqnum like '123456%' UNION all
SELECT * FROM Group82 where qqnum like '123456%' UNION all
SELECT * FROM Group83 where qqnum like '123456%' UNION all
SELECT * FROM Group84 where qqnum like '123456%' UNION all
SELECT * FROM Group85 where qqnum like '123456%' UNION all
SELECT * FROM Group86 where qqnum like '123456%' UNION all
SELECT * FROM Group87 where qqnum like '123456%' UNION all
SELECT * FROM Group88 where qqnum like '123456%' UNION all
SELECT * FROM Group89 where qqnum like '123456%' UNION all
SELECT * FROM Group90 where qqnum like '123456%' UNION all
SELECT * FROM Group91 where qqnum like '123456%' UNION all
SELECT * FROM Group92 where qqnum like '123456%' UNION all
SELECT * FROM Group93 where qqnum like '123456%' UNION all
SELECT * FROM Group94 where qqnum like '123456%' UNION all
SELECT * FROM Group95 where qqnum like '123456%' UNION all
SELECT * FROM Group96 where qqnum like '123456%' UNION all
SELECT * FROM Group97 where qqnum like '123456%' UNION all
SELECT * FROM Group98 where qqnum like '123456%' UNION all
SELECT * FROM Group99 where qqnum like '123456%' UNION all
SELECT * FROM Group100 where qqnum like '123456%'
SELECT * FROM Group101 where qqnum like '123456%' UNION all
SELECT * FROM Group102 where qqnum like '123456%' UNION all
SELECT * FROM Group103 where qqnum like '123456%' UNION all
SELECT * FROM Group104 where qqnum like '123456%' UNION all
SELECT * FROM Group105 where qqnum like '123456%' UNION all
SELECT * FROM Group106 where qqnum like '123456%' UNION all
SELECT * FROM Group107 where qqnum like '123456%' UNION all
SELECT * FROM Group108 where qqnum like '123456%' UNION all
SELECT * FROM Group109 where qqnum like '123456%' UNION all
SELECT * FROM Group110 where qqnum like '123456%' UNION all
SELECT * FROM Group111 where qqnum like '123456%' UNION all
SELECT * FROM Group112 where qqnum like '123456%' UNION all
SELECT * FROM Group113 where qqnum like '123456%' UNION all
SELECT * FROM Group114 where qqnum like '123456%' UNION all
SELECT * FROM Group115 where qqnum like '123456%' UNION all
SELECT * FROM Group116 where qqnum like '123456%' UNION all
SELECT * FROM Group117 where qqnum like '123456%' UNION all
SELECT * FROM Group118 where qqnum like '123456%' UNION all
SELECT * FROM Group119 where qqnum like '123456%' UNION all
SELECT * FROM Group120 where qqnum like '123456%' UNION all
SELECT * FROM Group121 where qqnum like '123456%' UNION all
SELECT * FROM Group122 where qqnum like '123456%' UNION all
SELECT * FROM Group123 where qqnum like '123456%' UNION all
SELECT * FROM Group124 where qqnum like '123456%' UNION all
SELECT * FROM Group125 where qqnum like '123456%' UNION all
SELECT * FROM Group126 where qqnum like '123456%' UNION all
SELECT * FROM Group127 where qqnum like '123456%' UNION all
SELECT * FROM Group128 where qqnum like '123456%' UNION all
SELECT * FROM Group129 where qqnum like '123456%' UNION all
SELECT * FROM Group130 where qqnum like '123456%' UNION all
SELECT * FROM Group131 where qqnum like '123456%' UNION all
SELECT * FROM Group132 where qqnum like '123456%' UNION all
SELECT * FROM Group133 where qqnum like '123456%' UNION all
SELECT * FROM Group134 where qqnum like '123456%' UNION all
SELECT * FROM Group135 where qqnum like '123456%' UNION all
SELECT * FROM Group136 where qqnum like '123456%' UNION all
SELECT * FROM Group137 where qqnum like '123456%' UNION all
SELECT * FROM Group138 where qqnum like '123456%' UNION all
SELECT * FROM Group139 where qqnum like '123456%' UNION all
SELECT * FROM Group140 where qqnum like '123456%' UNION all
SELECT * FROM Group141 where qqnum like '123456%' UNION all
SELECT * FROM Group142 where qqnum like '123456%' UNION all
SELECT * FROM Group143 where qqnum like '123456%' UNION all
SELECT * FROM Group144 where qqnum like '123456%' UNION all
SELECT * FROM Group145 where qqnum like '123456%' UNION all
SELECT * FROM Group146 where qqnum like '123456%' UNION all
SELECT * FROM Group147 where qqnum like '123456%' UNION all
SELECT * FROM Group148 where qqnum like '123456%' UNION all
SELECT * FROM Group149 where qqnum like '123456%' UNION all
SELECT * FROM Group150 where qqnum like '123456%' UNION all
SELECT * FROM Group151 where qqnum like '123456%' UNION all
SELECT * FROM Group152 where qqnum like '123456%' UNION all
SELECT * FROM Group153 where qqnum like '123456%' UNION all
SELECT * FROM Group154 where qqnum like '123456%' UNION all
SELECT * FROM Group155 where qqnum like '123456%' UNION all
SELECT * FROM Group156 where qqnum like '123456%' UNION all
SELECT * FROM Group157 where qqnum like '123456%' UNION all
SELECT * FROM Group158 where qqnum like '123456%' UNION all
SELECT * FROM Group159 where qqnum like '123456%' UNION all
SELECT * FROM Group160 where qqnum like '123456%' UNION all
SELECT * FROM Group161 where qqnum like '123456%' UNION all
SELECT * FROM Group162 where qqnum like '123456%' UNION all
SELECT * FROM Group163 where qqnum like '123456%' UNION all
SELECT * FROM Group164 where qqnum like '123456%' UNION all
SELECT * FROM Group165 where qqnum like '123456%' UNION all
SELECT * FROM Group166 where qqnum like '123456%' UNION all
SELECT * FROM Group167 where qqnum like '123456%' UNION all
SELECT * FROM Group168 where qqnum like '123456%' UNION all
SELECT * FROM Group169 where qqnum like '123456%' UNION all
SELECT * FROM Group170 where qqnum like '123456%' UNION all
SELECT * FROM Group171 where qqnum like '123456%' UNION all
SELECT * FROM Group172 where qqnum like '123456%' UNION all
SELECT * FROM Group173 where qqnum like '123456%' UNION all
SELECT * FROM Group174 where qqnum like '123456%' UNION all
SELECT * FROM Group175 where qqnum like '123456%' UNION all
SELECT * FROM Group176 where qqnum like '123456%' UNION all
SELECT * FROM Group177 where qqnum like '123456%' UNION all
SELECT * FROM Group178 where qqnum like '123456%' UNION all
SELECT * FROM Group179 where qqnum like '123456%' UNION all
SELECT * FROM Group180 where qqnum like '123456%' UNION all
SELECT * FROM Group181 where qqnum like '123456%' UNION all
SELECT * FROM Group182 where qqnum like '123456%' UNION all
SELECT * FROM Group183 where qqnum like '123456%' UNION all
SELECT * FROM Group184 where qqnum like '123456%' UNION all
SELECT * FROM Group185 where qqnum like '123456%' UNION all
SELECT * FROM Group186 where qqnum like '123456%' UNION all
SELECT * FROM Group187 where qqnum like '123456%' UNION all
SELECT * FROM Group188 where qqnum like '123456%' UNION all
SELECT * FROM Group189 where qqnum like '123456%' UNION all
SELECT * FROM Group190 where qqnum like '123456%' UNION all
SELECT * FROM Group191 where qqnum like '123456%' UNION all
SELECT * FROM Group192 where qqnum like '123456%' UNION all
SELECT * FROM Group193 where qqnum like '123456%' UNION all
SELECT * FROM Group194 where qqnum like '123456%' UNION all
SELECT * FROM Group195 where qqnum like '123456%' UNION all
SELECT * FROM Group196 where qqnum like '123456%' UNION all
SELECT * FROM Group197 where qqnum like '123456%' UNION all
SELECT * FROM Group198 where qqnum like '123456%' UNION all
SELECT * FROM Group199 where qqnum like '123456%' UNION all
SELECT * FROM Group200 where qqnum like '123456%'
6.查找数据所在的表与列
declare @str varchar(100)
set @str='123456' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like
''%'+@str+'%'')
print ''所在的表及字段: ['+b.name+'].['+a.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
可能和机器配置有关，也有可能查询数据量过于庞大，查询速度很慢，可能会有down机...
相关日志:
- 搜索
- 文章归档
-
- 2023年7月 (19)
- 2023年6月 (20)
- 2023年5月 (4)
- 2022年11月 (11)
- 2022年10月 (9)
- 2022年7月 (22)
- 2022年6月 (39)
- 2022年5月 (17)
- 2022年4月 (1)
- 2017年3月 (1)
- 2016年11月 (1)
- 2015年11月 (6)
- 2015年8月 (24)
- 2015年7月 (43)
- 2015年6月 (28)
- 2015年5月 (34)
- 2015年4月 (38)
- 2015年3月 (35)
- 2015年2月 (28)
- 2015年1月 (31)
- 2014年12月 (14)
- 2014年11月 (8)
- 2014年10月 (8)
- 2014年9月 (7)
- 2014年8月 (13)
- 2014年7月 (22)
- 2014年6月 (26)
- 2014年5月 (14)
- 2014年4月 (16)
- 2014年3月 (13)
- 2014年2月 (17)
- 2014年1月 (23)
- 2013年12月 (19)
- 2013年11月 (18)
- 2013年10月 (17)
- 2013年9月 (15)
- 2013年8月 (21)
- 2013年7月 (15)
- 2010年8月 (1)
- 最近发表
- 站点信息
-
- 文章总数:782
- 页面总数:2
- 分类总数:14
- 标签总数:522
- 评论总数:359
- 浏览总数:624719