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
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
GO
Exec sp_SearchObjectByValue
Null ,
Null ,
' totio ' ,
' Sysname ' ,
1