在《根据某一个值,查询到对应的表和字段(V2.0)》 中,在处理bit,datetime,smalldatetime数据类型字段上,存在Bug。
我们在V2.0上举例子
测试环境代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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数据类型字段测试
其实我们只是想查询数值为2333,所对应的表和字段,但当test数据库中存在一个表数据类型为bit字段,而且有一个数据为1,bit数据类型的这一字段也会被Select出来。
原因是,查询过程中,2333被转换成bit,变成了1,这样就给Select出来了。
2.datedate/smalldatetime数据类型字段测试
我们这里并不想Select出来TableBitNDateTime表里的字段Date(除非某一特殊需要,我们真的要那样一个结果),但它给Select出来了,原因是字段Date的数据默认格式是:"mon dd yyyy hh:miAM(或 PM)"(不同的时区默认格式可能不同)。
原因找出来了,我们就来解决它,在原来V2.0的版本上增加一个判断bit,datetime,smalldatetime这样的过程,完整代码如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 , ' 2333 ' , Null , 0
ok了,第一个查询搜索值为'0',所对应的表及字段,表TableBitNDateTime中的字段BitX(bit类型)有存在为0的数据。
第二个查询,没有返回记录,这里bit类型的字段给过滤掉了,所以没有返回表TableBitNDateTime中的字段BitX(bit类型)。
2.检查是否能查询到datetime,smalldatetime数据类型的数据
没有返回如何记录,好的,能实现了,真是太高兴了。
(完)