怎样查询：所有表中的含有“@163.com”字段的内容？
我有一个sql server 2005的数据库，里面有大约一百多个表，是一个网站的后台数据库。
我想请教大家：怎样用一条Sql 语句，把这一百多个表中，每个表中含有@163.com的字段检索出来，也就是想知道所有163电子邮箱的内容检索出来。
一百多个表的结构都不是完全相同的。其中某个表的结构示例如下：
col1 col2 col3 webmail email add
jack 18 2600 arlg@163.com XXX 北京市东城区
rose 24 rose@163.com jack@163.com XYZ 北京市景山区
john 22 2700 nodress nonumber noadd
kate 163.com 2800 adf sdfs sdfsss
halo 32 hlo@yaho.com kkk kkkkk 长春某某
检索结果如下：
col1 col2 col3 webmail email add
jack 18 2600 arlg@163.com XXX 北京市东城区
rose 24 rose@163.com jack@163.com XYZ 北京市景山区
所有表 字符串
[解决办法]
这是2008上可以执行的，不知道2005能不能执行，先给你试试吧：
第一步，创建存储过程：
CREATE proc spFind_Column_In_DB
(
@type int,--类型：1为文字类型、2为数值类型
@str nvarchar(100)--需要搜索的名字
)
as
--创建临时表存放结果
create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
if @type=1
begin
declare curTable cursor fast_forward
for
select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99)
end
else
begin
declare curTable cursor fast_forward
for
select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122)
end
open curtable
fetch next from curtable into @tbl,@col
while @@FETCH_STATUS=0
begin
set @sql='if exists (select * from '+@tbl+' where '
if @type=1
begin
set @sql += @col + ' like ''%'+@str +'%'')'
end
else
begin
set @sql +=@col + ' in ('+@str+'))'
end
set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
--print @sql
exec (@sql)
fetch next from curtable into @tbl,@col
end
close curtable
deallocate curtable
select * from #tbl
第二步：执行：
EXEC spFind_Column_In_DB 1,'@163.com'
[解决办法]
是这样吗：
select t.name as table_name,
c.name as column_name,
c.column_id
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where c.name like '%163.com%'
另外，你上面的那种格式，col1，col2，col3，webmail，email，add 这些字段分别是什么意思呢
[解决办法]
一百多个表的结构不完全相同,检索结果的字段数也不完全相同,结果集没法合并.
且结果集的个数是不定的.
建议只针对邮箱或可能邮箱信息的字段进行检索,性能比较好.
[解决办法]
/*
追加描述：
1、改脚本回去遍历每个数据库的每个架构下面的所有表的列
2、在消息选项卡里面会列出表和列以及查询语句
3、结果存储在临时表#Results。您可以查询这个表，在同一会话中进一步细化的结果，或删除GROUP BY查看详细的数据。
警告：
*因为这个脚本要遍历数据库中所有的表和列，它可能需要很长的时间来处理。
*您最初的测试可以运行在一个单一的表或列，看看你的系统负载情况。
*/
DECLARE @MaxRows INT ;
DECLARE @MinRows INT ;
DECLARE @FilterSchema NVARCHAR(255) ;
DECLARE @FilterTable NVARCHAR(255) ;
DECLARE @FilterColumn NVARCHAR(255) ;
DECLARE @Characters NVARCHAR(MAX) ;
-- 过滤表的最小和最大的行数，用此来限定目标表的范围
-- a. MinRows = 0 表示搜索所有的表, 1 表示搜索包含有数据的表.
-- b. MaxRows = null 表示搜索所有的表, > 0 表示搜索的表中的总行数小于这个值.
SET @MaxRows=1000 ;
SET @MinRows=1 ;
-- 下面三个变量分别用来配置架构、表和列的；这些值都使用 Like %Name% 模糊查询
-- Names are compared using Like %Name%
SET @FilterSchema=NULL ;
SET @FilterTable=NULL ;
SET @Filtercolumn=NULL ;
-- 要搜索的文本值
SET @Characters='a'
--**********************Script begins****************************
--***************************************************************
SET NOCOUNT ON
DECLARE @SchemaT TABLE
(
RowID INT IDENTITY(1 , 1) ,
SchemaName NVARCHAR(MAX) ,
TableName NVARCHAR(MAX) ,
ColumnName NVARCHAR(MAX)
)
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
DROP TABLE #Results
END
CREATE TABLE #Results
(
RowID INT IDENTITY(1 , 1) ,
RSchemaName NVARCHAR(MAX) DEFAULT '' ,
RTableName NVARCHAR(MAX) DEFAULT '' ,
RColumnName NVARCHAR(MAX) DEFAULT '' ,
Value NTEXT DEFAULT ''
)
DECLARE @LoopNo INT ,
@TotalRows INT ,
@Schema NVARCHAR(MAX) ,
@Table NVARCHAR(MAX) ,
@Column NVARCHAR(MAX) ,
@SQL NVARCHAR(MAX) ,
@ParamDef NVARCHAR(MAX) ,
@DataExists BIT
DECLARE @ReturnValue NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(MAX)
--****************************************************************************************************
-- 查找满足条件的所有信息到表变量@SchemaT里面，其中包括架构、表名、列名和表的大小
--****************************************************************************************************
INSERT INTO @SchemaT (SchemaName , TableName , ColumnName)
SELECT Sch = t.Sch , Tbl = REPLACE(REPLACE(t.Tbl , '[' , '[[') , ']' , ']]') , Col = c.name
FROM (
SELECT s.Name AS Sch , t.name AS Tbl , t.object_id , SUM(p.rows) AS NumCount
FROM sys.schemas s
LEFT JOIN sys.tables t
ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE p.index_id IN (0 , 1) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1 -- row-data only , not LOB
AND (
s.name LIKE '%'+@FilterSchema+'%'
OR @FilterSchema IS NULL
)
AND (
t.name LIKE '%'+@FilterTable+'%'
OR @FilterTable IS NULL
)
GROUP BY s.Name , t.name , t.object_id
HAVING (
SUM(p.rows) >= @MinRows
AND (
SUM(p.rows) <= @MaxRows
OR @MaxRows IS NULL
)
)
) T
INNER JOIN sys.columns C
ON T.object_id = c.object_id
INNER JOIN sys.types P
ON C.system_type_id = p.system_type_id
WHERE (
p.name LIKE '%char%'
OR p.name LIKE '%text%'
)
AND (
c.name LIKE '%'+@FilterColumn+'%'
OR @FilterColumn IS NULL
)
ORDER BY Sch , Tbl , Col
--**********************************************************************************
-- 拼接动态语句，并执行把结果插入到临时表 #Results 里面
--**********************************************************************************
SELECT @LoopNo=1 , @TotalRows=MAX(RowID)
FROM @SchemaT
PRINT '总计出现次数 = '+CAST(@TotalRows AS NCHAR(5))+CHAR(13)
WHILE @LoopNo <= @TotalRows
BEGIN
SELECT @Schema=SchemaName , @Table=TableName , @Column=ColumnName
FROM @SchemaT
WHERE RowID = @LoopNo
SET @SQL='SELECT '+QUOTENAME(@Column)+' FROM '+QUOTENAME(@Schema)+'.'+QUOTENAME(@Table)+' WHERE CHARINDEX('''+@Characters+''','+QUOTENAME(@Column)+') > 0'
--**********************************************************************************
-- 如果包含有指定的字符串，就输出查询语句和出现的此处以及表信息
--**********************************************************************************
IF @TotalRows >0
BEGIN
PRINT '出现位置: '+QUOTENAME(@Schema)+'.'+QUOTENAME(@Table)+'.'+QUOTENAME(@Column)
PRINT '出现次数: '+CAST(@LoopNo AS NCHAR(5))
PRINT '查询语句: '+@SQL+CHAR(13)
END
--**********************************************************************************
SET @ParmDefinition='@ReturnValueOUT NVARCHAR(MAX) OUTPUT'
INSERT INTO #Results (Value)
EXECUTE sp_executesql
@SQL ,
@ParmDefinition ,
@ReturnValueOUT=@ReturnValue OUTPUT
UPDATE #Results
SET RTableName=@Table , RColumnName=@Column , RSchemaName=@Schema
WHERE RTableName = ''
SET @LoopNo=@LoopNo+1
END
--**********************************************************************************
--显示结果
--**********************************************************************************
SELECT COUNT(*) AS Occurrences , RSchemaName , RTableName , RColumnName
FROM #Results
GROUP BY RSchemaName , RTableName , RColumnName
[解决办法]
可以借助系统表,动态产生查询的SQL脚本.
select 'select ['+b.name+'] from ['+a.name+'] where ['+b.name+'] like ''%@163.com%'' '
from sys.tables a
inner join sys.columns b on a.object_id=b.object_id
inner join sys.types c on b.system_type_id=c.system_type_id
where c.name in('char','nchar','varchar','nvarchar')
[解决办法]