create table #tmp (tbName varchar(200),tbRows int,tbReserved varchar(20),
tbData varchar(20),tbIndexSize varchar(20),tbUnUsed varchar(20))insert #tmp exec sp_MSForEachTable ' exec sp_spaceused ''?'''
select * from #tmp
select * from #tmp where tbrows=80drop table #tmp
tbData varchar(20),tbIndexSize varchar(20),tbUnUsed varchar(20))insert #tmp exec sp_MSForEachTable ' exec sp_spaceused ''?'''
select * from #tmp
select * from #tmp where tbrows=80drop table #tmp
tbname 表名
tbrows 记录数
tbREserved 保留空间
tbData 使用空间
tbIndexSize 索引使用空间
tbUnUsed 未用空间
我只知道查所有的表名是:use XXX select name from sysobjects
帮你顶,SQL Server 版里很多高手,因该没问题的。
select i.rows,object_name(o.id) tb
from sysobjects o inner join sysindexes i
on i.id=o.id
where o.xtype='u'
and
(i.indid = 0 or i.indid = 1)
and
i.rows=80
sysobjects.name,
sysindexes.rows
FROM sysindexes with(nolock)
JOIN sysobjects with(nolock)
ON sysindexes.id = sysobjects.id AND sysobjects.xtype = 'u'
WHERE sysindexes.indid in(0, 1) and rows=80
ORDER By sysobjects.name ASC