SqlServer查询整个数据库的所有表所有数据行数及占空间大小

1、查询整个数据库的用户表

select * from sysobjects where xtype='U'

2、统计整个数据库的用户表总数

select COUNT(*) from sysobjects where xtype='U'

3、查询整个数据库用户表的数据行数

SELECT a.name,b.rows FROM  sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE a.xtype='U' AND b.indid=1

4、统计整个数据库用户表的所有数据行总数

SELECT SUM(b.rows) FROM  sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE a.xtype='U' AND b.indid=1

5、查询整个数据库用户表占用空间大小（KB）

SELECT a.name 数据表,8*b.dpages 占用空间KB FROM  sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE a.xtype='U' AND b.indid=1

6、统计整个数据库用户表所占用空间大小（MB）

SELECT 8*SUM(b.dpages)/1024 占用空间MB FROM  sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE a.xtype='U' AND b.indid=1

备注：

sysobjects表参考地址：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15

sysindexes表参考地址：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-compatibility-views/sys-sysindexes-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15