這種情況需要寫存儲過程,進行全庫搜索。代碼如下:
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?'%varchar%'
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
註意:其中“張三”為要查找的字符串,可以替換成其他的,如果查詢的字符串超長,需要在定義變量時適當擴大長度。
可以替換成其他的,如果查詢的字符串超長,需要在定義變量時適當擴大長度。