数据库量和条数统计

MYSQL 查询 数据库量和条数

-- 查询所有数据库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
-- where table_schema = 'your_database_name'
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

-- 查询所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
-- where table_schema = 'your_database_name'
order by table_schema asc, data_length desc, index_length desc;

-- 查询单表每个索引大小
select
sum(stat_value) pages,
table_name,
index_name,
concat(round(sum(stat_value)/1000000 * @@innodb_page_size, 2), 'M') size
from mysql.innodb_index_stats
where
table_name = 'user_info_index'
and database_name = 'db_my_test'
and stat_description LIKE 'Number of pages in the index'
group by table_name, index_name;

SQL SERVER 查询 数据库量和条数
第一种：

select  

        sum(c.row_count) as datacount  
from    sys.indexes a ,  
        sys.objects b ,  
        sys.dm_db_partition_stats c  
where   a.[object_id] = b.[object_id]  
        AND b.[object_id] = c.[object_id]  
        AND a.index_id = c.index_id  
        AND a.index_id < 2  
        AND b.is_ms_shipped = 0 

第二种：

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