根据某一个值，查询到对应的表和字段(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
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
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
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
没有返回如何记录，好的，能实现了,真是太高兴了。
(完)
