要做整站检索，不知道该怎么写了，求高手赐教！！

[aysunzheng]

如题！

[cd731107]

全文检索

[aysunzheng]

具体用什么办法，存储过程啊，什么的！

[aysunzheng]

多表的！

[cd731107]

--数据库模糊查询内容

--下面把like ''%abcdefg%''中的abcdefg改为你查的字段的值
declare @TableName varchar(100),@ColumnName  varchar(100),@sql varchar(8000)
create table #1(tablename varchar(100),columnname varchar(100)) 

declare cursor1 cursor for         --定义游标cursor1
select sysobjects.name,syscolumns.name from syscolumns  
            inner join sysobjects on syscolumns.id = sysobjects.id
            where  sysobjects.xtype = 'u'               --使用游标的对象(跟据需要填入select文)
open cursor1 


fetch next from cursor1 into @TableName,@ColumnName  --将游标向下移1行，获取的数据放入之前定义的变量

while @@fetch_status=0           --判断是否成功获取数据
begin
--print()
--print()
	set @sql='insert #1 select '''+@TableName+''','''+@ColumnName
       +''' where (select count(1) from '+@TableName
       +' where cast('+@ColumnName+' as varchar(100)) like ''%abcdefg%'')>0'  --把这里的abcdefg改为你查的字段的值
	exec(@sql)

fetch next from cursor1 into @TableName,@ColumnName  --将游标向下移1行
end

close cursor1                   --关闭游标
deallocate cursor1

select 表名=tablename,字段名=columnname from #1
drop table #1

[cd731107]

--来自小F
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
*/


Create  PROC  getFieldAll(@t VARCHAR(100),@v VARCHAR(20), @f VARCHAR(1000) OUT )
AS 
BEGIN    
    DECLARE @name VARCHAR(40)
    DECLARE @S NVARCHAR(1000),@C INT     
    SET @f='(2006)德旌法协执字第325-1号'
    
    EXEC('declare cur cursor for select name from syscolumns where id=object_id('''+@t+''') and  xtype in(SELECT xtype 

FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))')
    
    OPEN cur 
    FETCH NEXT FROM cur INTO @name
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @S='select @s=count(1) from '+@t+' where ['+@name +'] like ''%'+@v+'%'''
        EXEC sp_executesql @S,N'@s int out',@C OUT 
        IF @C>0
            SET @f=@f+@name+','        
        FETCH NEXT FROM cur INTO @name
    END 
    CLOSE cur
    DEALLOCATE cur 
    RETURN 
END   

go
EXEC sp_msforeachtable 
        @command1 = N'declare @l varchar(1000); exec getFieldAll ''?'',''aa_1'',@l out;if @l<>'''' select ''?'' 表名,@l 

列名
        '

[aysunzheng]

这么凶，不懂啊！！

[AcHerat]

会懂的。

[cd731107]

不是已经有很多注释了吗，傻瓜式操作，不麻烦的

[--小F--]

http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx

[rucypli]

用全文索引

[aysunzheng]

小F那个还行，还是有写不明白，方便写下QQ，请教一下！