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