alter proc spFind_Column_In_DB
 (
     @type int,--类型：1为文字类型、2为数值类型
     @str nvarchar(100)--需要搜索的名字
 )
 as
     --创建临时表存放结果
     create table #tbl(PK int identity primary key ,tbl sysname,col sysname)
     declare @tbl nvarchar(300),@col sysname,@sql nvarchar(1000)
     if @type=1 
     begin
         declare curTable cursor fast_forward
         for 
             select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
             where o.type_desc='user_table' and user_type_id in (167,175,231,239,35,99)
      end
     else
     begin 
         declare curTable cursor fast_forward
         for 
         select '['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c inner join sys.objects o on c.object_id=o.object_id
             where o.type_desc='user_table' and user_type_id in (56,48,52,59,60,62,106,108,122)
     end
     open curtable
     fetch next from curtable into @tbl,@col
     while @@FETCH_STATUS=0
     begin
         set @sql='if exists (select * from '+@tbl+' where '
         if @type=1
         begin
             set @sql += @col + ' like ''%'+@str +'%'')'
         end
         else 
         begin
             set @sql +=@col + ' in ('+@str+'))'
         end
 
         set @sql += ' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'
         --print @sql
         exec (@sql)
         fetch next from curtable into @tbl,@col
     end
     close curtable 
     deallocate curtable
     select * from #tbl

------解决方案--------------------
才20多张表。死查死查也查出来了。

SQL code
SELECT  'SELECT NAME FROM ['+ NAME+'] WHERE NAME=''张三''' 
FROM SYSOBJECTS  S WHERE TYPE='U'
AND EXISTS(SELECT 1 FROM dbo.syscolumns WHERE S.ID=ID AND NAME='NAME')

------解决方案--------------------
SQL code
declare @id int
declare @sql nvarchar(1000)
set @id=0
while exists(select 1 from syscolumns a inner join sysobjects b on a.id=b.id where a.name='name' and a.id>@id and b.type='U')
begin
select top 1 @sql='select '''+b.name+''' as tbname,* from '+ b.name +' where name=''zhangsan'''
from syscolumns a inner join sysobjects b on a.id=b.id
where a.name='name' and a.id>@id and b.type='U'
select @sql
exec (@sql)
select top 1 @id=b.id from syscolumns a inner join sysobjects b on a.id=b.id
where a.name='name' and a.id>@id and b.type='U' order by b.id
end

------解决方案--------------------
这个ID是dbo.syscolumns的ID