根据某一个值，查询到对应的表和字段(V3.0)

 在《根据某一个值，查询到对应的表和字段(V2.0)》 中，在处理bit,datetime,smalldatetime数据类型字段上，存在Bug。

 

我们在V2.0上举例子

测试环境代码：

 

代码

Use  Test
Go

If   object_id ( ' TableBitNDateTime ' )  Is   Not   Null
     Drop   Table  TableBitNDateTime
Go
Create   Table  TableBitNDateTime
(
    ID  int   Identity ( 1 , 1 ),
    BitX  bit ,
    Date  datetime ,
     Constraint  PK_TableBitNDateTime  Primary   Key (ID  Asc )
)    
Go
Insert   Into  TableBitNDateTime(BitX,Date)
     Select   0 , ' 20090101 06:20:46 '   Union   All
     Select   0 , ' 20090101 16:12:11 '   Union   All
     Select   1 , ' 20090101 18:45:23 '   Union   All     
     Select   0 , ' 20090101 21:51:29 '  
Go

 1.bit数据类型字段测试

Exec  sp_SearchObjectByValue  ' test ' , Null , ' 2333 ' , Null , 0  

其实我们只是想查询数值为2333，所对应的表和字段，但当test数据库中存在一个表数据类型为bit字段，而且有一个数据为1,bit数据类型的这一字段也会被Select出来。

原因是，查询过程中，2333被转换成bit，变成了1，这样就给Select出来了。

 

 2.datedate/smalldatetime数据类型字段测试

Exec  sp_SearchObjectByValue  ' test ' , Null , ' P ' , Null , 1  

 

 我们这里并不想Select出来TableBitNDateTime表里的字段Date(除非某一特殊需要，我们真的要那样一个结果)，但它给Select出来了，原因是字段Date的数据默认格式是："mon dd yyyy hh:miAM（或 PM）"（不同的时区默认格式可能不同）。

 

 

 原因找出来了，我们就来解决它，在原来V2.0的版本上增加一个判断bit,datetime,smalldatetime这样的过程，完整代码如下：

 

代码

Use  Master
Go
If   Object_id ( ' sp_SearchObjectByValue ' )  Is   Not   Null
     Drop   Proc  sp_SearchObjectByValue
Go
/* 根据某一个值，查询到对应的表和字段(V3.0) Andy 22009-12-2


*/
Create   Proc  sp_SearchObjectByValue
(
     @DataBaseName     sysname = null ,
     @TableName         sysname = null ,
     @Value             sql_variant = null ,
     @DataType          nvarchar ( 512 ) = null ,
     @IsByLike          bit = 0
)
As
Set  Nocount  On
/*
参数说明：
    @DataBaseName    数据库名.            为Null的时候，遍历所有数据库
    @TableName        表名.                为Null的时候，遍历所有表
    @Value            要搜索的值.            当@Value为Null的时候，@IsByLike 设置无效
    @DataType        要搜索的值所对应的数据类型.    定义如：numeric(18,2),int,money,nvarchar(60)
    @IsByLike        是否要模糊搜索.
    

Exec sp_SearchObjectByValue
        @DataBaseName=PayRoll,
        @TableName=null,
        @Value='A',
        @DataType=null,
        @IsByLike=0

*/


Declare   @Sql              nvarchar ( 4000 ),
         @TypeName         sysname,
         @TypeID              int ,
         @Typelength          smallint ,
         @TypePrecision      smallint ,
         @Typescale          smallint ,
         @Error              nvarchar ( 1024 ),
         @TypeIn              nvarchar ( 100 )  /* 在V3.0版本上增加的，用于判断排除的数据类型  */

If   DB_ID ( @DataBaseName )  Is   Null   And    @DataBaseName   Is   Not   Null
Begin
     Raiserror   50001  N ' 无效的数据库名!请重新设置参数@DataBaseName. '
     Return
End

If   @DataType   Is   Not   Null
Begin
     Select      @TypeName =Left ( @DataType , Charindex (N ' ( ' , @DataType + N ' ( ' ) - 1 ),
             @TypeID = TYPE_ID( @TypeName )

     If   @TypeID   Is   Null
     Begin
         Raiserror   50001  N ' 无效的数据类型!请重新设置参数@DataType. '
         Return
     End

     Begin  Try
         If   Charindex (N ' , ' , @DataType ) > 0
         Begin
             Set   @TypePrecision = Substring ( @DataType , Charindex (N ' ( ' , @DataType ) + 1 , Charindex (N ' , ' , @DataType ) - Charindex (N ' ( ' , @DataType ) - 1 )
             Set   @Typescale = Substring ( @DataType , Charindex (N ' , ' , @DataType ) + 1 , Charindex (N ' ) ' , @DataType ) - Charindex (N ' , ' , @DataType ) - 1 )
         End
         Else   If   Charindex (N ' ( ' , @DataType ) > 0
             Set   @Typelength =   Substring ( @DataType , Charindex (N ' ( ' , @DataType ) + 1 , Charindex (N ' ) ' , @DataType ) - Charindex (N ' ( ' , @DataType ) - 1 )
                     * Case   When   @TypeID   In  ( 239 , 231 )  Then   2   Else   1   End
        
     End  Try
     Begin  Catch
         Raiserror   50001  N ' 无效的数据类型!请重新设置参数@DataType. '
         Return
     End  Catch

     Begin  Try
         Set   @Sql = N ' Declare @x  ' + @DataType + '  Set @x=Convert( ' + @DataType + ' ,@Value) '
         Exec  sp_executesql  @Sql ,N ' @Value sql_variant ' , @Value
     End  Try
     Begin  Catch
         Set   @Error = ERROR_MESSAGE()
         Raiserror   50001   @Error
         Return
     End  Catch
End


Set   @TypeIn = N ''

/* 当传入的@Value参数不是0 Or 1 ，就不要搜索Bit类型的字段 */
If   convert ( nvarchar ( max ), @Value ) Not In (N ' 0 ' ,N ' 1 ' ) 
     Set   @TypeIn = @TypeIn + ' ,104 '

/* 当传入的@Value参数等于A,P,AM,PM ，就不要搜索smalldatetime,datetime类型的字段,可以根据实际的需要是否去掉这一过滤功能 */
If   convert ( nvarchar ( max ), @Value )  In (N ' A ' ,N ' AM ' ,N ' P ' ,N ' PM ' )  And   @IsByLike = 1
     Set   @TypeIn = @TypeIn + ' ,58,61 '

    


If   Object_id ( ' tempdb..#TableSql ' )  Is   Not   Null
     Drop   Table  #TableSql

If   Object_id ( ' tempdb..#TableReturn ' )  Is   Not   Null
     Drop   Table  #TableReturn

Create   Table  #TableSql (Sql  nvarchar ( 4000 ))
Create   Table  #TableReturn (DBName sysname,TableName sysname,FieldName sysname)

Declare  cur_database  Cursor  Local  For
     Select  name  From  sys.databases  Where  Name  Not   In ( ' master ' , ' model ' , ' msdb ' , ' tempdb ' )  And  (name = @DataBaseName   Or   @DataBaseName   Is   Null )
     For   Read   Only

Open  cur_database
Fetch   Next   From  cur_database  Into   @DataBaseName
While   @@FETCH_STATUS = 0
Begin
     If   @Value   Is   Null   
         Set   @Sql = N ' Use  ' + Quotename ( @DataBaseName ) + ' ;
        Select  '' If Exists(Select 1 From  ' + Quotename ( @DataBaseName ) + ' .. '' +Quotename(b.name)+ ''  Where  '' +Quotename(a.name)+ ''  Is Null ) Select  '' +Quotename( ''' + @DataBaseName + ''' , '''''''' )+ '' , '' +Quotename(b.name, '''''''' )+ '' , '' +Quotename(a.name, '''''''' )
                From syscolumns As a 
                    Inner Join sysobjects As b On b.id=a.id 
                        And b.xtype= '' U '''
     Else   If   @IsByLike   = 1
         Set   @Sql = N ' Use  ' + Quotename ( @DataBaseName ) + ' ;
        Select  '' If Exists(Select 1 From  ' + Quotename ( @DataBaseName ) + ' .. '' +Quotename(b.name)+ ''  Where  '' +Quotename(a.name)+ ''  Like  '''' % '' +Convert(nvarchar(max),@Value)+ '' % '''' ) Select  '' +Quotename( ''' + @DataBaseName + ''' , '''''''' )+ '' , '' +Quotename(b.name, '''''''' )+ '' , '' +Quotename(a.name, '''''''' )
                From syscolumns As a 
                    Inner Join sysobjects As b On b.id=a.id 
                        And b.xtype= '' U '''
     Else
         Set   @Sql = N ' Use  ' + Quotename ( @DataBaseName ) + ' ;
        Select  '' Begin Try Declare @x  '' +TYPE_NAME(a.xusertype)+ ''  Set @x=Convert( '' +TYPE_NAME(a.xusertype)+ '' , '''''' +Convert(nvarchar(max),@Value)+ '''''' ) If Exists(Select 1 From  ' + Quotename ( @DataBaseName ) + ' .. '' +Quotename(b.name)+ ''  Where  '' +Quotename(a.name)+ ''  =  '''''' +Convert(nvarchar(max),@Value)+ '''''' ) Select  '' +Quotename( ''' + @DataBaseName + ''' , '''''''' )+ '' , '' +Quotename(b.name, '''''''' )+ '' , '' +Quotename(a.name, '''''''' )+  ''  End Try Begin Catch End Catch ; ''
                From syscolumns As a 
                    Inner Join sysobjects As b On b.id=a.id 
                        And b.xtype= '' U '''

     If   @TableName   Is   Not   Null
         Set   @Sql = @Sql + '  And b.name = ' + Quotename ( @TableName , '''' )

     Set   @Sql = @Sql + '  And a.xusertype  Not In(34,35,241,99,173,165 ' + @TypeIn + ' ) '

     If   @TypeID > 0
         Set   @Sql = @Sql + '  And a.xusertype= ' + Rtrim ( @TypeID )

     If   @Typelength > 0
         Set   @Sql = @Sql + '  And a.length= ' + Rtrim ( @Typelength )

     If   @TypePrecision > 0
         Set   @Sql = @Sql + '  And a.xprec= ' + Rtrim ( @TypePrecision )

     If   @Typescale > 0
         Set   @Sql = @Sql + '  And a.xscale= ' + Rtrim ( @Typescale )

     If   @Sql > ''  
         Insert   Into  #TableSql   Exec  sp_executesql  @Sql ,N ' @Value sql_variant ' , @Value
    
     Declare  cur_Inner  Cursor   For   Select  Sql  From  #TableSql
     Open  cur_Inner
         Fetch   Next   From  cur_Inner  Into   @Sql
     While   @@FETCH_STATUS = 0
     Begin     
         Begin  Try
             Insert   Into  #TableReturn  Exec ( @Sql )
         End  Try
         Begin  Catch
         End  Catch
         Fetch   Next   From  cur_Inner  Into   @Sql
     End
     Close  cur_Inner
     Deallocate  cur_Inner
     Delete   From  #TableSql

     Fetch   Next   From  cur_database  Into   @DataBaseName
End
CLose  cur_database
Deallocate  cur_database

Select   *   From  #TableReturn

Drop   Table  #TableSql
Drop   Table  #TableReturn
Go

 

 测试V3.0的查询功能：

 

 1.检查是否能查询到bit数据类型的数据

 

Exec  sp_SearchObjectByValue  ' test ' , Null , ' 0 ' , Null , 0  
Exec  sp_SearchObjectByValue  ' test ' , Null , ' 2333 ' , Null , 0  

 

 ok了，第一个查询搜索值为'0'，所对应的表及字段，表TableBitNDateTime中的字段BitX(bit类型)有存在为0的数据。

 第二个查询，没有返回记录，这里bit类型的字段给过滤掉了，所以没有返回表TableBitNDateTime中的字段BitX(bit类型)。

 

2.检查是否能查询到datetime,smalldatetime数据类型的数据   

Exec  sp_SearchObjectByValue  ' test ' , Null , ' P ' , Null , 1  

没有返回如何记录，好的，能实现了,真是太高兴了。

 

 

 

 (完)