查询整个数据库中,含有特定值,所在的表和字段:
if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟只需要修改红色的部分:if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟只需要修改红色的部分:if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
还是不行吧
SELECT TOP 1000 [ID]
,[QQNum]
,[Nick]
,[Age]
,[Gender]
,[Auth]
,[QunNum]
FROM [GroupData1].[dbo].[Group10]数据结构这个样子 最后要查询QQNum=""
你帮我看看吧 谢谢
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟只需要修改红色的部分:if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
还是不行吧
SELECT TOP 1000 [ID]
,[QQNum]
,[Nick]
,[Age]
,[Gender]
,[Auth]
,[QunNum]
FROM [GroupData1].[dbo].[Group10]数据结构这个样子 最后要查询QQNum=""
你帮我看看吧 谢谢
又是这个qq群的数据试试这个:
declare @sql nvarchar(max)
declare @qqnum nvarchar(30)set @sql = ''
set @qqnum = 'xxx'select @sql = @sql +
' union all SELECT [ID],[QQNum],[Nick],[Age],[Gender],[Auth],[QunNum]
FROM [GroupData1].[dbo].['+ name +']' +
' where QQNum='''+@qqnum+''''
from sys.tables
set @sql = STUFF(@sql,1,10,'')--select @sqlexec(@sql)
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟只需要修改红色的部分:if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
还是不行吧
SELECT TOP 1000 [ID]
,[QQNum]
,[Nick]
,[Age]
,[Gender]
,[Auth]
,[QunNum]
FROM [GroupData1].[dbo].[Group10]数据结构这个样子 最后要查询QQNum=""
你帮我看看吧 谢谢
又是这个qq群的数据试试这个:
declare @sql nvarchar(max)
declare @qqnum nvarchar(30)set @sql = ''
set @qqnum = 'xxx'select @sql = @sql +
' union all SELECT [ID],[QQNum],[Nick],[Age],[Gender],[Auth],[QunNum]
FROM [GroupData1].[dbo].['+ name +']' +
' where QQNum='''+@qqnum+''''
from sys.tables
set @sql = STUFF(@sql,1,10,'')--select @sqlexec(@sql)
消息 208,级别 16,状态 1,第 1 行
对象名 'GroupData1.dbo.spt_fallback_db' 无效。这样
大哥 我自己要改哪些 注释一下吧?谢谢了 对SQL不是很熟只需要修改红色的部分:if OBJECT_ID('temp_search_table') is not null
drop table temp_search_table
gocreate table temp_search_table
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go
declare @sql nvarchar(max);
declare @search_str nvarchar(100);set @sql = ''
set @search_str = '%163.com%'; --你要查询的数据select @sql = @sql + 'insert into temp_search_table '+
'select '''+t.name +''' as table_name,''' +
c.name+ ''' as column_name, ['+
c.name + '] from ['+t.name +
'] where ['+c.name +'] like '''+@search_str+''';'
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
inner join sys.types tp
on c.system_type_id = tp.system_type_id
and c.user_type_id = tp.user_type_id
and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'
--select @sqlexec(@sql)
--最后查找,你找到的内容
select *
from temp_search_table
还是不行吧
SELECT TOP 1000 [ID]
,[QQNum]
,[Nick]
,[Age]
,[Gender]
,[Auth]
,[QunNum]
FROM [GroupData1].[dbo].[Group10]数据结构这个样子 最后要查询QQNum=""
你帮我看看吧 谢谢
又是这个qq群的数据试试这个:
declare @sql nvarchar(max)
declare @qqnum nvarchar(30)set @sql = ''
set @qqnum = 'xxx'select @sql = @sql +
' union all SELECT [ID],[QQNum],[Nick],[Age],[Gender],[Auth],[QunNum]
FROM [GroupData1].[dbo].['+ name +']' +
' where QQNum='''+@qqnum+''''
from sys.tables
set @sql = STUFF(@sql,1,10,'')--select @sqlexec(@sql)
消息 208,级别 16,状态 1,第 1 行
对象名 'GroupData1.dbo.spt_fallback_db' 无效。这样你试试这个:declare @sql nvarchar(max)
declare @qqnum nvarchar(30)set @sql = ''
set @qqnum = 'xxx'select @sql = @sql +
' union all SELECT [ID],[QQNum],[Nick],[Age],[Gender],[Auth],[QunNum]
FROM [GroupData1].[dbo].['+ name +']' +
' where QQNum='''+@qqnum+''''
from sys.tables
where name like '%Group%'
set @sql = STUFF(@sql,1,10,'')--select @sqlexec(@sql)
执行成功了 但是没有select出来诶
执行成功了 但是没有select出来诶
你得把set @qqnum = 'xxx' 中的xxx改成,你要查找的qq号码
我已经改过了 只是显示了命令已成功完成 select语句好像没执行吧
我已经改过了 只是显示了命令已成功完成 select语句好像没执行吧把语句,改成这样,可以打印出具体要执行的语句,你看看这个语句对不对:
declare @sql nvarchar(max)
declare @qqnum nvarchar(30)set @sql = ''
set @qqnum = 'xxx'select @sql = @sql +
' union all SELECT [ID],[QQNum],[Nick],[Age],[Gender],[Auth],[QunNum]
FROM [GroupData1].[dbo].['+ name +']' +
' where QQNum='''+@qqnum+''''
from sys.tables
where name like '%Group%'
set @sql = STUFF(@sql,1,10,'')select @sql--exec(@sql)