sql server 如何在全库中查找数据在哪个表

1.查找字段在库中哪个表

如果要查找FName 

select   a.name,b.name   from   syscolumns a   inner   join   sysobjects   b   on   a.id=b.id 

where   a.name   =   'FName'

2.查找数据值在哪个表中

 如果要查找 制动器组

 

declare @cloumns varchar(40)

declare @tablename varchar(40)

declare @str varchar(40)

declare @counts int

declare @sql nvarchar(2000)

declare MyCursor Cursor For 

Select a.name as Columns, b.name as TableName from syscolumns a,sysobjects b,systypes c 

where a.id = b.id

and b.type = 'U' 

and a.xtype=c.xtype

and c.name like '%char%'

set @str='制动器组'

Open MyCursor

Fetch next From MyCursor Into @cloumns,@tablename

While(@@Fetch_Status = 0)

Begin

 set @sql='select  @tmp_counts=count(*) from ' +@tablename+ ' where ' +@cloumns+' = ''' +@str+ ''''

execute sp_executesql  @sql,N'@tmp_counts int out',@counts out

 if @counts>0

 begin

 print '表名为:'+@tablename+',字段名为'+@cloumns

 end

Fetch next From MyCursor Into @cloumns,@tablename

End

Close MyCursor

Deallocate MyCursor

 

 

 

亲测可用，但是第二项查找数据会报错，希望哪个大神帮忙解决报错问题。