create PROCEDURE [dbo].[usp_Tool_GetDataOfKey]
@Value nvarchar(50)
AS
declare @strTableName nvarchar(100)
declare @strTableID nvarchar(50)
declare @strColumnName nvarchar(100)
declare @strSql nvarchar(500)
create table #KeyTable(Table_Name nvarchar(100),Column_Name nvarchar(50))
declare Table_Cursor cursor
for
select name,id from sysobjects where xtype='U'
open Table_Cursor
fetch next from Table_Cursor into @strTableName,@strTableID
while(@@fetch_status=0)
begin
declare Column_Cursor cursor
for
select name from syscolumns where id=@strTableID and (((xtype=239 or xtype=231) and length>30) or ((xtype=175 or xtype=167) and length>15))
open Column_Cursor
fetch next from Column_Cursor into @strColumnName
while(@@fetch_status=0)
begin
select @strSql='if exists(select * from '+@strTableName+' where '+@strColumnName+' ='''+@Value+''')'
select @strSql=@strSql+'insert into #KeyTable(Table_Name,Column_Name) values('''+@strTableName+''','''+@strColumnName+''')'
print @strSql
exec(@strSql)
fetch next from Column_Cursor into @strColumnName
end
close Column_Cursor
deallocate Column_Cursor
fetch next from Table_Cursor into @strTableName,@strTableID
end
close Table_Cursor
deallocate Table_Cursor
select * from #KeyTable
sqlsever通过输入值查找系统相关的表和字段
最新推荐文章于 2022-07-25 14:05:36 发布