如何根据给定的值在数据库中查找出值对应的表和字段(MsSQL)

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

 

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