查看数据库及表的大小和所占用空间大小

查看数据库及表的大小和所占用空间大小

Sqlserver

exec sp_spaceused  '表名'  --取得表占用空間 
exec sp_spaceused ”  --数据库所有空間

 

下面的sql也可以通过看表的记录行数来估算大小

SELECT a.name, b.rows 
FROM sysobjects AS a INNER JOIN 
sysindexes AS b ON a.id = b.id 
WHERE (a.type = 'u') AND (b.indid IN (0, 1)) 
ORDER BY b.rows DESC
 

也可以写成函数，方便调用

CREATE FUNCTION [dbo].[fnGetTableRows](@TableName varchar(50)) RETURNS int 
   
  AS 
 BEGIN 
   --距离(千米) 
  DECLARE @Rows int 
      SELECT @Rows=b.rows 
FROM sysobjects AS a INNER JOIN 
sysindexes AS b ON a.id = b.id 
WHERE (a.type = 'u') AND (b.indid IN (0, 1))  and a.name=@TableName  

  RETURN @Rows 
 END

调用： 

declare @i int
set @i=dbo.fnGetTableRows('T_User')
print @i

 

Mysql

# check table rows

select table_name,table_rows from  information_schema.`TABLES` where TABLE_SCHEMA='数据库名' order by table_rows desc limit 10;

# check database size  这里的单位是 Mb,如果要看 GB多除一个1024即可
#SELECT table_schema as "DB Name",Round(Sum(data_length + index_length) / 1024 / 1024 , 1) as "DB Size in MB"  FROM   information_schema.tables  GROUP  BY table_schema;

 

# check table size 这里的单位是 Mb,如果要看 GB多除一个1024即可

SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES where table_schema = '数据库名' ORDER BY (data_length + index_length) DESC ;