查寻数据库中的某个数据

第一步，首先要了解SQL中，下面三个系统表中字段的含义 select * from sysobjects 数据库中包含的表 select * from syscolumns 数据库中包含的列 select * from systypes 数据类型的定义

第二步，写SQL语句（下面的语句以字符为例） CREATE TABLE #a (

biaomingcheng VARCHAR(800), liemingcheng VARCHAR(800), chaxun VARCHAR(800),

) --用来保存结果 CREATE TABLE #b (

tabName VARCHAR(40), sql VARCHAR(800),

) --用来保存中间运行语句 declare @key varchar(30)

set @key = 'SPH00240054' --替换为要查找的字符串

DECLARE @tabName VARCHAR(40),@colName VARCHAR(40) DECLARE @sql VARCHAR(8000) declare @tsql varchar(800)

DECLARE tabCursor CURSOR FOR

SELECT name from sysobjects --WHERE xtype = 'u' AND name <> 'dtproperties' OPEN tabCursor

FETCH NEXT from tabCursor INTO @tabName WHILE @@fetch_status = 0 BEGIN

set @tsql = ''

DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) and xtype in (167,175,231,239) OPEN colCursor

FETCH NEXT from colCursor INTO @colName WHILE @@fetch_status = 0 BEGIN set @sql=''

SET @sql = 'if(exists(select * from ' + @tabName + ' where '

SET @sql = @sql + @colName + ' like ''%' + @key + '%''))begin INSERT INTO #a VALUES ('''

set @sql = @sql + @tabName +''''+ ','''+@colName+''''+ ','''+@key+''''+') end'

set @tsql =@sql + ';' exec(@tsql)

INSERT INTO #b VALUES (@tabName,@tsql) --可以查看己执行过的语句

FETCH NEXT from colCursor INTO @colName END

CLOSE colCursor

DEALLOCATE colCursor

FETCH NEXT from tabCursor INTO @tabName END

CLOSE tabCursor

DEALLOCATE tabCursor

select * from #a order by biaomingcheng drop table #a drop table #b

因为要挺索整个数据了库，所以如果数据库越大，运行的时间可能会慢。此过程主要运用了游标，对于，不懂游标使用的人来说，是个不错的例子。

整理于 2012-5-11