ms sql 查找数据库mydb中所有的表
发布网友
发布时间：2024-05-02 12:21
共2个回答
热心网友
时间：2024-07-23 16:05
@strword varchar(50)
AS
BEGIN
set nocount on
create table #result
(
OID int not null identity,
zian varchar(50) null,
tablename varchar(50) null,
zianleixing varchar(50) null,
icount int null
)
declare @count int
,@zianname varchar(50)
,@tablename varchar(50)
,@zianleixing varchar(50)
,@sql Nvarchar(800)
declare cur_LocationList cursor fast_forward for
select a.name ,b.name,c.name from syscolumns a
inner join sysobjects b
on a.id=b.id and b.xtype='u' and lower(a.name) not in('desc','order','top','left','bottom','right')
inner join systypes c
on a.xtype=c.xusertype
and c.name in('varchar','char','nvarchar','nchar','decimal','float','int','money')
open cur_LocationList
fetch next from cur_LocationList into @zianname,@tablename,@zianleixing
while @@fetch_status = 0
begin
set @count=0
if @zianleixing='money'
set @sql='select @a=count(*) from ['+@tablename+'] where convert(varchar(20),'+@zianname+') like ''%'+@strword+'%'''
else
set @sql='select @a=count(*) from ['+@tablename+'] where '+@zianname+' like ''%'+@strword+'%'''
--print @sql
exec sp_executeSql @sql,N'@a int output',@count output
--print @count
if @count>=1
begin
insert into #result(zian,tablename,icount,zianleixing) values (@zianname,@tablename,@count,@zianleixing)
end
fetch next from cur_LocationList into @zianname,@tablename,@zianleixing
end
close cur_LocationList
deallocate cur_LocationList
select zian as "字段名",tablename as "表名",icount as "出现次数",zianleixing as "字段类型" from #result
order by "表名","字段名"
drop table #result
set nocount off
END
GO
执行
exec Search_tablename '001'
热心网友
时间：2024-07-23 16:05
如果是我
我会选择读取所有表的数据
然后再使用查找
NET使用 gridview 就可以查看所有的数据勒
