Question

sql数据中 怎么查询 一个库的所有表中含有123这个数字

Answer 1

是不是要遍历数据库呀
declare @str varchar(100)
set @str='张三' --要搜索的字符串

declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where convert(varchar,['+a.name+']) like ''%'+@str+'%'')
print ''select ['+a.name+'] from ['+b.name+']'''
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
--所查列的字段类型
and a.xusertype in(175,239,231,167,56,60,108,106)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb

Answer 2

全文搜索也许能做到，去试试全文搜索，否则就编程序遍历的搜吧，先遍历所有表，再遍历所有的字段。

追问

全文搜索在哪里？ Sql2000

追答

http://wenku.baidu.com/view/e26e44fe700abb68a982fb6a.html

Answer 3

你说的是在sqlserver管理界面上，还是用编程？如果是编程，是什么语言？
遍历每个表、每行、每个字段不就行了吗？

追问

sql 语句的话 怎么写？ 我都忘了 嘿嘿 能告诉我么

追答

不知道，我只知道用.net的语句来遍历。

追问

.net 怎么遍历呢？ 能告诉我白

追答

'''
''' 判断数据库中是否含有某个字符串
'''
''' SqlConnection
''' 查找的字符
''' 是否含有
''' vb.net
Private Function ExistString(ByVal Conn As SqlConnection, ByVal FindString As String) As Boolean
Conn.Open()
For Each row As DataRow In Conn.GetSchema("TABLES").Rows
Dim tn As String = row("TABLE_NAME").ToString
Dim Dt As New DataTable
Dim Da As New SqlDataAdapter("select * from [" & tn & "]", Conn)
Da.Fill(Dt)
For Each r As DataRow In Dt.Rows
For Each c As DataColumn In Dt.Columns
If r(c).ToString = FindString Then
MsgBox("表名=" & tn & "字段=" & c.ColumnName)
Return True '如果想要找全部，则加上这句
End If
Next
Next
Next

Return False
Conn.Close()
End Function