数据库遍历

create table #tt 
(
name nvarchar(50),
rows char(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
go
--排序所有的表，按行数
insert #tt exec sp_msforeachtable 'exec sp_spaceused "?"'
select * from #tt order by convert(int,replace(rows,'kb','')) desc
--DROP TABLE  #TT
 
  declare @str varchar(100)  
  set     @str='3.5'     --要搜索的字符串   
  declare   @s   varchar(8000)  
  declare   tb   cursor   local   for  
  select   s='if   exists(select   1   from   ['+b.name+']   where   ['+a.name+']   like   ''%'+@str+'%'')  
  print   ''select * from  ['+b.name+'] where ['+a.name+'] = "'+@str+'" '''  
  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)  
     and   b.name in  (select name from #tt where convert(int,rows) between 20 and 5000 )
  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

语句解析
select top 100 * from syscolumns  a 
【系统字段表,当前数据库所有对象的字段名】

select top 100 * from sysobjects b
【系统对象表,当前数据库所有的对象(表,视图,过程,索引,关健字,约束等)】

select top 100 * from systypes  c 
【字段类型表,a.xtype=c.xusertype】

xtype='U'是用户表
a.status>=0这个条件是没有用的,MSSQL 系统字段表的status都是>=0的

1.查询数据库内含字段名为[name]的所有表名
select OBJECT_NAME(id) from syscolumns where id in(
select id from sysobjects where type='U')
and name='name'

--2008查找某数据库中的列是否存在某个值
create 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

--使用例子，查询库中存在aaa这个值的列：
exec  spFind_Column_In_DB  1,'aaa'

方法二

if OBJECT_ID('temp_search_table') is not null
   drop table temp_search_table
go

--存放找到的表、列、值
create table temp_search_table 
(
table_name nvarchar(100),
column_name nvarchar(100),
column_search_value nvarchar(max)
)
go

declare @sql nvarchar(max);
declare @search_str nvarchar(100);

set @sql = ''
set @search_str = '%163.com%';   --你要找的值

select @sql = @sql + 'insert into temp_search_table '+
                     'select '''+t.name +''' as table_name,''' + 
                     c.name+ ''' as column_name, ['+
                     c.name + '] from ['+t.name + 
                     '] where ['+c.name +'] like '''+@search_str+''';'
       
from sys.tables t
inner join sys.columns c   on t.object_id = c.object_id 
inner join sys.types tp    on c.system_type_id = tp.system_type_id
           and c.user_type_id = tp.user_type_id
           and tp.name in ('char','varchar','nchar','nvarchar')
where t.name <> 'temp_search_table'


--select @sql

exec(@sql)


--最后查找,你找到的内容
select *
from temp_search_table

--按内容查找存储过程： 
select distinct name
from sysobjects o, syscomments s
where o.id = s.id 
and o.xtype = 'P'
and text like '%is_zx=''清''%'