--查询当前数据库的数据量计数,按表分组 use --数据库 select max(a.rowcnt) 计数,b.[name] 表名 from sysindexes a join sys.objects b on b.object_id=a.id where b.type='U' group by b.[name] having max(a.rowcnt) >0 order by 1 desc
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT) EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'',COUNT(*) FROM ?' SELECT TableName, RowCnt FROM #temp ORDER BY TableName
select b.name,a.rowcnt from sysindexes a,sysobjects b where a.id=b.id and a.indid<2 and b.xtype='u' --and rowcnt>100000
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
use --数据库
select max(a.rowcnt) 计数,b.[name] 表名
from sysindexes a
join sys.objects b on b.object_id=a.id
where b.type='U'
group by b.[name]
having max(a.rowcnt) >0
order by 1 desc
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'',COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
where a.id=b.id and a.indid<2 and b.xtype='u' --and rowcnt>100000
--统计数据库里每个表的详细情况
EXEC sp_MSforeachtable @command1="sp_spaceused '?'" --获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
select LO.name, rows from sysindexes TI right Join
(Select [name], [id] From SysObjects where xType = 'U') LO
on TI.id = LO.id
where TI.indid in (0,1)
select b.name,a.rowcnt from sysindexes a,sysobjects b
where a.id=b.id and a.indid<2 and b.xtype='u'