查询数据库数据量

--查询所有表名以及数据数量

select b.name as tablename , 

a.rowcnt as datacount
from sysindexes a ,
sysobjects b
where a.id = b.id
and a.indid < 2
and objectproperty(b.id, 'IsMSShipped') = 0
order by datacount desc

--查询所有数据数量

SELECT sum(a.datacount)as datacount FROM (select b.name as tablename ,
a.rowcnt as datacount
from sysindexes a ,
sysobjects b
where a.id = b.id
and a.indid < 2
and objectproperty(b.id, 'IsMSShipped') = 0)a

--根据条件筛选数据库下所有表的数据量

declare @deptid nvarchar(100);
declare @sql nvarchar(100);
declare @tableName nvarchar(100);
set @deptid='4f67f45c97ca48cdb4736e540413684b'
create TABLE #temp ( RowCnt INT)  --创建临时表
declare cur1 cursor for   --创建游标
SELECT object_name (i.id) tableName
FROM sysindexes i
INNER JOIN sysObjects o
ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2
ORDER BY tableName
open cur1  --打开游标
fetch next from cur1 into @tableName  --游标停在第一条数据
while @@fetch_status=0 --0：成功取到数据   -2：没有取到数据
begin
exec('insert into #temp select count(1) as rowCnt from '+@tableName+' where DeptID='''+ @deptid+'''')
fetch next from cur1 into @tableName
end
SELECT sum(RowCnt)as sumCount from #temp
close cur1
deallocate cur1
drop table #temp