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

 

 

If   Object_id (N ' [sp_SearchObjectByValue] ' )  Is   Not   Null  
Drop   Proc   [ sp_SearchObjectByValue ]
Go
/* 根据某一个值，查询到对应的表和字段(V2.0) OK_008 2009-5-24 */
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 )
 
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
 
 
 
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) '
 
     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

 

 

 调用举例：

 

use  test
GO
Exec  sp_SearchObjectByValue 
     Null ,
     Null ,
     ' totio ' ,
     ' Sysname ' ,
     1