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

ゞ浴缸里的玫瑰 2021-10-29 14:32 456阅读 0赞

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

### 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 ;