查找指定字段所在的表单

/*
create by zhanglei
20061029

exec sp_searchTable '','','自增字段'

*/

alter procedure sp_searchTable
@TableName varchar(125),
@colName varchar(120),
@SubInfo varchar(200)
as

set nocount on
--创建保存结果信息的表格
if exists(select 1 from sysobjects where id = object_id(N'srchTbl'))
drop table srchtbl

create table srchtbl(FTbl varchar(120), FCol varchar(120), FInfo varchar(200))

 
declare @FTableName varchar(120)
   

declare tableCursor cursor for
select name from sysobjects
where xtype ='U' and (charindex(@tableName,name) >0 or isnull(@TableName,'') = '')
open tableCursor

fetch next from tableCursor into @FtableName
while @@fetch_status =0
begin
  declare @srchStr varchar(8000)
  set @SrchStr = ' select 1 from '+ @FtableName+ ' where (1=0) '
  select @srchStr = @SrchStr + ' or charindex(''' + @subInfo+''','+c.name+') >0 '
  from syscolumns c
  left join systypes t on c.xuserType = t.xuserType
  where c.id = object_id(@FtableName) and t.name <> N'image'
    and (charindex(@ColName,c.name) >0 or isnull(@TableName,'') = '')

  --执行动态sql语句，查询结果
  exec(@srchStr)
  if @@rowcount > 0
  begin
    insert into srchtbl(FTbl,FInfo) values(@FTableName,@SubInfo)
  end

--查找下一个表
  fetch next from tableCursor into @FtableName
end

close tableCursor
deallocate tableCursor

--显示结果
select * from srchtbl
set nocount off