怎样查询：所有表中的含有“@163.com”字段的内容？

[arlgil1]

我有一个sql server 2005的数据库，里面有大约一百多个表，是一个网站的后台数据库。

我想请教大家：怎样用一条Sql 语句，把这一百多个表中，每个表中含有@163.com的字段检索出来，也就是想知道所有163电子邮箱的内容检索出来。

一百多个表的结构都不是完全相同的。其中某个表的结构示例如下：

col1 col2 col3 webmail email add
jack 18 2600 [e-mail] XXX 北京市东城区
rose 24 [e-mail] [e-mail] XYZ 北京市景山区
john 22 2700 nodress nonumber noadd
kate 163.com 2800 adf sdfs sdfsss
halo 32 [e-mail] kkk kkkkk 长春某某


检索结果如下：
col1 col2 col3 webmail email add
jack 18 2600 [e-mail] XXX 北京市东城区
rose 24 [e-mail] [e-mail] 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'

[LongRui888]

是这样吗：

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 这些字段分别是什么意思呢

[唐诗三百首]

一百多个表的结构不完全相同,检索结果的字段数也不完全相同,结果集没法合并. 且结果集的个数是不定的. 建议只针对邮箱或可能邮箱信息的字段进行检索,性能比较好.

[arlgil1]

是啊，你说的不错，我也打算这样做。 就是要对一百多个表逐个手动输入语句查询太麻烦了。

引用 3 楼 ap0405140 的回复:

一百多个表的结构不完全相同,检索结果的字段数也不完全相同,结果集没法合并. 且结果集的个数是不定的. 建议只针对邮箱或可能邮箱信息的字段进行检索,性能比较好.

[姐只会简单SQL语句]

/* 追加描述： 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

[唐诗三百首]

引用 4 楼 arlgil1 的回复:

是啊，你说的不错，我也打算这样做。 就是要对一百多个表逐个手动输入语句查询太麻烦了。

可以借助系统表,动态产生查询的SQL脚本.

select 'select ['+b.name+'] from ['+a.name+'] where ['+b.name+'] like ''[e-mail]%'' '
 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')

[arlgil1]

你的这个不行啊，你是对所有表的列名进行查询是否含有“163.com”，我要的是对所有表下面的各列的内容进行查询，相当于全文检索。 那些col1... ...都是列的名字。

引用 2 楼 yupeigu 的回复:

是这样吗：

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 这些字段分别是什么意思呢

[kevin87923]

6楼不是可以吗

[LongRui888]

引用 7 楼 arlgil1 的回复:

你的这个不行啊，你是对所有表的列名进行查询是否含有“163.com”，我要的是对所有表下面的各列的内容进行查询，相当于全文检索。 那些col1... ...都是列的名字。 [quote=引用 2 楼 yupeigu 的回复:] 是这样吗：

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 这些字段分别是什么意思呢

[/quote] 给改了一下，你再试试，这次是搜索表中所有包含 特定字符串的列，以及列的内容：

if OBJECT_ID('temp_search_table') is not null
   drop table temp_search_table
go

create 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 @sql

exec(@sql)


--最后查找,你找到的内容
select *
from temp_search_table

[sqlkxr]

  if object_id('test') is not null drop table test
  go
  create table test(email nvarchar(50))
  insert into test select '[e-mail]'
  union all
  select '[e-mail]'
  union all
  select '[e-mail]'


  select * from test where email like '%163.com%'

[Alessandro_]

[dawugui]

以下的这些方法也许对你用.

------------------第一种方法----------------------

CREATE PROC sp_ValueSearch
@value sql_variant,  --要搜索的数据
@precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN

--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')

--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
    SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
        WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
        WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
        ELSE N'=@value' END
ELSE
BEGIN
    SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
    IF @sql LIKE N'%char' or @sql LIKE N'%text'
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%char' or name LIKE N'%text'
        SELECT @sql=N' LIKE N''%''+CAST(@value as '
            +CASE 
                WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
                ELSE 'varchar(8000)' END
            +N')+N''%'''
    END
    ELSE IF @sql LIKE N'%datetime'
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%datetime'
        SET @sql=N'=@value'    
    END
    ELSE IF @sql LIKE N'%int' 
        OR @sql LIKE N'%money' 
        OR @sql IN(N'real',N'float',N'decimal',N'numeric')
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%int' 
            OR name LIKE N'%money' 
            OR name IN(N'real',N'float',N'decimal')
        SET @sql=N'=@value'    
    END
    ELSE
        SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))

DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
    +QUOTENAME(USER_NAME(o.uid))
    +N'.'+QUOTENAME(o.name)
    +N' WHERE '+QUOTENAME(c.name)
    +@sql,
    N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
    +N',N'+QUOTENAME(c.name,N'''')
    +N',N'+QUOTENAME(QUOTENAME(t.name)+CASE 
        WHEN t.name IN (N'decimal',N'numeric')
        THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
        WHEN t.name=N'float'
            OR t.name like N'%char'
            OR t.name like N'%binary'
        THEN N'('+CAST(c.prec as varchar)+N')'
        ELSE N'' END,N'''')
    +N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
    AND c.xusertype=t.xusertype
    AND t.xtype=tt.xtype
    AND OBJECTPROPERTY(o.id,N'IsUserTable')=1

OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
    SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
    EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
    FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #

exec sp_ValueSearch '要搜索的值',1 --1或不输入（即默认值1）精确匹配
exec sp_ValueSearch '要搜索的值',0 --不等于1，模糊匹配

---------------第二种方法------------------

Create   PROC xb_GetTableNameAndColNameForValue
 @value varchar(200)
AS
--求test库中包含值为@value的表和列名

--存储表名和列名
IF object_id('tabss') IS NOT NULL 
 exec('drop table tabss')
CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))

--查询某表某列是否包含某个值
IF object_id('ysgs') IS NOT NULL
 exec('drop proc ysgs')
exec('create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')

--将结果存入tabss表中
EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0  insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'


GO


/*调用
exec xb_GetTableNameAndColNameForValue 'aa_1'
SELECT * FROM tabss
*/

------------------------第三种方法---------------------
declare @name nvarchar(100)
declare cur cursor for select name from sysobjects where type = 'U'
open cur
fetch next from cur into @name
WHILE @@FETCH_STATUS = 0
begin

    declare @sql nvarchar(500),@s varchar(500)
    set @s =''
    set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) ' 
    exec sp_executesql @sql,N'@s varchar(500) out',@s out
    if len(@s) > 0 
        exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''')
    fetch next from cur into @name
end
close cur
DEALLOCATE cur

------第4种方法---------（来自小梁）

CREATE TABLE tb(id int,col varchar(20))
CREATE TABLE tb2(id int,data varchar(20))

INSERT tb VALUES(1,'中国');
INSERT tb VALUES(2,'liangck')

INSERT tb2 VALUES(1,'China')
GO

CREATE PROCEDURE dbo.FindString
    @string NVARCHAR(100)
AS

    DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'
            DECLARE @str NVARCHAR(4000);
            SELECT
                @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%' 
                                                + @string + ' %'''''',
                               c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
                AND c.xtype=t.xtype
                AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar'');

            SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
            CREATE TABLE #tb(a int);
            INSERT #tb(a) EXEC(@str);
            IF EXISTS(SELECT * FROM #tb)
                PRINT ''?''
        ';
    EXEC sp_MsforeachTable @SQL;
GO

EXEC dbo.FindString N'中国'

GO
DROP PROCEDURE dbo.FindString
DROP TABLE tb,tb2

[btxp163]

[我是流氓我怕谁啊]