精华内容
下载资源
问答
  • 1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \as index_length,sum(data_length+index_length...

    1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位

    mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \

    as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

    +--------------------+---------------+--------------+---------------+

    | table_schema | data_length | index_length | sum |

    +--------------------+---------------+--------------+---------------+

    | information_schema | 2734.92757511 | 86.27539063 | 2821.20296574 |

    +--------------------+---------------+--------------+---------------+

    2、查看该实例下各个库大小

    mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, \

    sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, \

    count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;

    +--------------------+---------------+---------------+-------------+--------+------------+

    | table_schema | total_mb | data_mb | index_mb | tables | today |

    +--------------------+---------------+---------------+-------------+--------+------------+

    | data_1234567890 | 2820.59610939 | 2734.39689064 | 86.19921875 | 65 | 2015-11-02 |

    | mysql | 0.60579967 | 0.53744030 | 0.06835938 | 14 | 2015-11-02 |

    | information_schema | 0.00781250 | 0.00000000 | 0.00781250 | 35 | 2015-11-02 |

    +--------------------+---------------+---------------+-------------+--------+------------+

    3、查看单个库的大小

    mysql> select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size, \

    concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size, \

    concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free, \

    concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size\

    from information_schema.tables where table_schema = 'erongtu_tyb2014';

    +-----------+------------------+-----------+------------+

    | data_size | max_data_size | data_free | index_size |

    +-----------+------------------+-----------+------------+

    | 2734.40mb | 83483426815.99mb | 14.06mb | 86.19mb |

    +-----------+------------------+-----------+------------+

    4、查看单个表的状态

    mysql> show table status from data_1234567890 where name = 'data_1234567890_ss' \G

    *************************** 1. row ***************************

    Name: data_1234567890_ss

    Engine: InnoDB

    Version: 10

    Row_format: Compact

    Rows: 840065

    Avg_row_length: 477

    Data_length: 401473536

    Max_data_length: 0

    Index_length: 0

    Data_free: 6291456

    Auto_increment: 882251

    Create_time: 2015-09-07 17:24:18

    Update_time: NULL

    Check_time: NULL

    Collation: utf8_general_ci

    Checksum: NULL

    Create_options:

    Comment:

    1 row in set (0.00 sec)

    5、查看单库下所有表的状态

    mysql> select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024) \

    as index_mb, ((data_length+index_length)/1024/1024) as all_mb, table_rows \

    from tables where table_schema = 'data_1234567890';

    +---------------------------+---------------+-------------+---------------+------------+

    | table_name | data_mb | index_mb | all_mb | table_rows |

    +---------------------------+---------------+-------------+---------------+------------+

    | ss_daccount | 0.23437500 | 0.10937500 | 0.34375000 | 4481 |

    | ss_daccount_log | 2.48262787 | 0.58496094 | 3.06758881 | 27248 |

    | ss_daccount_type | 0.00025558 | 0.00195313 | 0.00220871 | 8 |

    | ss_daccountlog | 221.61502457 | 22.66113281 | 244.27615738 | 1045462 |

    | ss_dactives | 0.00178146 | 0.00195313 | 0.00373459 | 7 |

    | ss_dadmin | 0.00268173 | 0.00195313 | 0.00463486 | 19 |

    | ss_dadmin_log | 0.36599731 | 0.05175781 | 0.41775513 | 5191 |

    | ss_dadmin_nav | 0.01562500 | 0.00000000 | 0.01562500 | 46 |

    | ss_dadmin_role | 0.01562500 | 0.01562500 | 0.03125000 | 5 |

    | ss_dadvertisement | 0.07812500 | 0.00000000 | 0.07812500 | 486 |

    | ss_dadvertisement_click | 382.87500000 | 0.00000000 | 382.87500000 | 1023350 |

    | ss_dadvertisement_content | 0.10937500 | 0.01562500 | 0.12500000 | 105 |

    | ss_dapplication | 3.23010635 | 0.02441406 | 3.25452042 | 1859 |

    | ss_dapplication_bak | 2.25843048 | 0.01269531 | 2.27112579 | 738 |

    | ss_dapplication_comment | 0.88685226 | 0.05566406 | 0.94251633 | 5454 |

    | ss_dapplication_material | 0.00187683 | 0.00195313 | 0.00382996 | 16 |

    | ss_dapplication_user | 0.90316010 | 0.10839844 | 1.01155853 | 8861 |

    | ss_darea | 1.05803299 | 0.88769531 | 1.94572830 | 45051 |

    | ss_dauthentication | 0.00072861 | 0.00195313 | 0.00268173 | 13 |

    | ss_dbbs_relatedlink | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

    | ss_dborrow | 0.04732895 | 0.00390625 | 0.05123520 | 153 |

    | ss_dborrow_collection | 27.61576080 | 13.99023438 | 41.60599518 | 221169 |

    | ss_dborrow_lz | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_dborrow_tender | 24.62931824 | 18.27050781 | 42.89982605 | 147411 |

    | ss_dcash | 6.40177155 | 1.97949219 | 8.38126373 | 42807 |

    | ss_dcp_block | 0.04687500 | 0.01562500 | 0.06250000 | 37 |

    | ss_dcp_item | 0.09375000 | 0.00000000 | 0.09375000 | 134 |

    | ss_dedu_member | 0.01562500 | 0.00000000 | 0.01562500 | 13 |

    | ss_dinfo_article | 78.76256561 | 1.19433594 | 79.95690155 | 10038 |

    | ss_dinfo_channel | 0.01562500 | 0.00000000 | 0.01562500 | 56 |

    | ss_dinfo_comment | 0.32812500 | 0.00000000 | 0.32812500 | 1206 |

    | ss_dinfo_nav | 0.01562500 | 0.00000000 | 0.01562500 | 27 |

    | ss_dinfo_p2pdata | 0.90237427 | 0.29101563 | 1.19338989 | 29569 |

    | ss_dinfo_p2pdata_0 | 0.04687500 | 0.00000000 | 0.04687500 | 275 |

    | ss_dinfo_photo | 0.23437500 | 0.00000000 | 0.23437500 | 71 |

    | ss_dinfo_project | 0.06250000 | 0.00000000 | 0.06250000 | 67 |

    | ss_dinfo_seekdata | 35.53404236 | 0.64062500 | 36.17466736 | 5002 |

    | ss_dinfo_tagname | 0.01562500 | 0.00000000 | 0.01562500 | 18 |

    | ss_dinfo_testinfo | 0.01562500 | 0.00000000 | 0.01562500 | 21 |

    | ss_dinfo_video | 0.91273117 | 0.00781250 | 0.92054367 | 207 |

    | ss_djiao | 0.01562500 | 0.00000000 | 0.01562500 | 3 |

    | ss_dliberty | 3.79615784 | 0.27734375 | 4.07350159 | 23108 |

    | ss_dliberty_item | 3.08350754 | 1.24414063 | 4.32764816 | 51113 |

    | ss_dloan | 0.00000000 | 0.00390625 | 0.00390625 | 0 |

    | ss_dmarke | 0.01973343 | 0.00390625 | 0.02363968 | 125 |

    | ss_dmedal | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_dmember_comment | 0.00995636 | 0.00195313 | 0.01190948 | 87 |

    | ss_dmembers | 6.73762321 | 0.33203125 | 7.06965446 | 30717 |

    | ss_dmembers_identity | 0.10503769 | 0.00976563 | 0.11480331 | 784 |

    | ss_dnewyear | 0.00308609 | 0.00195313 | 0.00503922 | 32 |

    | ss_drecharge | 29.13045883 | 10.23925781 | 39.36971664 | 221220 |

    | ss_dsalon_click | 0.00586700 | 0.00195313 | 0.00782013 | 31 |

    | ss_dsalon_comment | 0.07812500 | 0.00000000 | 0.07812500 | 213 |

    | ss_dsalon_content | 0.14062500 | 0.00000000 | 0.14062500 | 653 |

    | ss_dsalon_ticket | 0.00093079 | 0.00195313 | 0.00288391 | 20 |

    | ss_dsignin | 0.37500000 | 0.00000000 | 0.37500000 | 7177 |

    | ss_dtemplate | 0.15721893 | 0.03808594 | 0.19530487 | 2838 |

    | ss_dwd_answers | 2.51562500 | 0.00000000 | 2.51562500 | 5420 |

    | ss_dwd_classify | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

    | ss_dwd_collection | 0.01562500 | 0.00000000 | 0.01562500 | 2 |

    | ss_dwd_questions | 1.51562500 | 0.00000000 | 1.51562500 | 3614 |

    | ss_dwget | 1894.69999695 | 13.02929688 | 1907.72929382 | 1187574 |

    | ss_dzhuanti_bolanhui | 0.00419235 | 0.00195313 | 0.00614548 | 20 |

    | click | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_account | 0.14062500 | 0.07812500 | 0.21875000 | 1871 |

    +---------------------------+---------------+-------------+---------------+------------+

    本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

    本文系统来源:php中文网

    TAG标签:大小数据库

    展开全文
  • 1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024as index_length,sum(data_length+index_length)/...

    1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位

    mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024

    as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

    +--------------------+---------------+--------------+---------------+

    | table_schema | data_length | index_length | sum |

    +--------------------+---------------+--------------+---------------+

    | information_schema | 2734.92757511 | 86.27539063 | 2821.20296574 |

    +--------------------+---------------+--------------+---------------+

    2、查看该实例下各个库大小

    mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,

    sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,

    count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;

    +--------------------+---------------+---------------+-------------+--------+------------+

    | table_schema | total_mb | data_mb | index_mb | tables | today |

    +--------------------+---------------+---------------+-------------+--------+------------+

    | data_1234567890 | 2820.59610939 | 2734.39689064 | 86.19921875 | 65 | 2015-11-02 |

    | mysql | 0.60579967 | 0.53744030 | 0.06835938 | 14 | 2015-11-02 |

    | information_schema | 0.00781250 | 0.00000000 | 0.00781250 | 35 | 2015-11-02 |

    +--------------------+---------------+---------------+-------------+--------+------------+

    3、查看单个库的大小

    mysql> select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size,

    concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size,

    concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free,

    concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size

    from information_schema.tables where table_schema = 'erongtu_tyb2014';

    +-----------+------------------+-----------+------------+

    | data_size | max_data_size | data_free | index_size |

    +-----------+------------------+-----------+------------+

    | 2734.40mb | 83483426815.99mb | 14.06mb | 86.19mb |

    +-----------+------------------+-----------+------------+

    4、查看单个表的状态

    mysql> show table status from data_1234567890 where name = 'data_1234567890_ss' G

    *************************** 1. row ***************************

    Name: data_1234567890_ss

    Engine: InnoDB

    Version: 10

    Row_format: Compact

    Rows: 840065

    Avg_row_length: 477

    Data_length: 401473536

    Max_data_length: 0

    Index_length: 0

    Data_free: 6291456

    Auto_increment: 882251

    Create_time: 2015-09-07 17:24:18

    Update_time: NULL

    Check_time: NULL

    Collation: utf8_general_ci

    Checksum: NULL

    Create_options:

    Comment:

    1 row in set (0.00 sec)

    5、查看单库下所有表的状态

    mysql> select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024)

    as index_mb, ((data_length+index_length)/1024/1024) as all_mb, table_rows

    from tables where table_schema = 'data_1234567890';

    +---------------------------+---------------+-------------+---------------+------------+

    | table_name | data_mb | index_mb | all_mb | table_rows |

    +---------------------------+---------------+-------------+---------------+------------+

    | ss_daccount | 0.23437500 | 0.10937500 | 0.34375000 | 4481 |

    | ss_daccount_log | 2.48262787 | 0.58496094 | 3.06758881 | 27248 |

    | ss_daccount_type | 0.00025558 | 0.00195313 | 0.00220871 | 8 |

    | ss_daccountlog | 221.61502457 | 22.66113281 | 244.27615738 | 1045462 |

    | ss_dactives | 0.00178146 | 0.00195313 | 0.00373459 | 7 |

    | ss_dadmin | 0.00268173 | 0.00195313 | 0.00463486 | 19 |

    | ss_dadmin_log | 0.36599731 | 0.05175781 | 0.41775513 | 5191 |

    | ss_dadmin_nav | 0.01562500 | 0.00000000 | 0.01562500 | 46 |

    | ss_dadmin_role | 0.01562500 | 0.01562500 | 0.03125000 | 5 |

    | ss_dadvertisement | 0.07812500 | 0.00000000 | 0.07812500 | 486 |

    | ss_dadvertisement_click | 382.87500000 | 0.00000000 | 382.87500000 | 1023350 |

    | ss_dadvertisement_content | 0.10937500 | 0.01562500 | 0.12500000 | 105 |

    | ss_dapplication | 3.23010635 | 0.02441406 | 3.25452042 | 1859 |

    | ss_dapplication_bak | 2.25843048 | 0.01269531 | 2.27112579 | 738 |

    | ss_dapplication_comment | 0.88685226 | 0.05566406 | 0.94251633 | 5454 |

    | ss_dapplication_material | 0.00187683 | 0.00195313 | 0.00382996 | 16 |

    | ss_dapplication_user | 0.90316010 | 0.10839844 | 1.01155853 | 8861 |

    | ss_darea | 1.05803299 | 0.88769531 | 1.94572830 | 45051 |

    | ss_dauthentication | 0.00072861 | 0.00195313 | 0.00268173 | 13 |

    | ss_dbbs_relatedlink | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

    | ss_dborrow | 0.04732895 | 0.00390625 | 0.05123520 | 153 |

    | ss_dborrow_collection | 27.61576080 | 13.99023438 | 41.60599518 | 221169 |

    | ss_dborrow_lz | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_dborrow_tender | 24.62931824 | 18.27050781 | 42.89982605 | 147411 |

    | ss_dcash | 6.40177155 | 1.97949219 | 8.38126373 | 42807 |

    | ss_dcp_block | 0.04687500 | 0.01562500 | 0.06250000 | 37 |

    | ss_dcp_item | 0.09375000 | 0.00000000 | 0.09375000 | 134 |

    | ss_dedu_member | 0.01562500 | 0.00000000 | 0.01562500 | 13 |

    | ss_dinfo_article | 78.76256561 | 1.19433594 | 79.95690155 | 10038 |

    | ss_dinfo_channel | 0.01562500 | 0.00000000 | 0.01562500 | 56 |

    | ss_dinfo_comment | 0.32812500 | 0.00000000 | 0.32812500 | 1206 |

    | ss_dinfo_nav | 0.01562500 | 0.00000000 | 0.01562500 | 27 |

    | ss_dinfo_p2pdata | 0.90237427 | 0.29101563 | 1.19338989 | 29569 |

    | ss_dinfo_p2pdata_0 | 0.04687500 | 0.00000000 | 0.04687500 | 275 |

    | ss_dinfo_photo | 0.23437500 | 0.00000000 | 0.23437500 | 71 |

    | ss_dinfo_project | 0.06250000 | 0.00000000 | 0.06250000 | 67 |

    | ss_dinfo_seekdata | 35.53404236 | 0.64062500 | 36.17466736 | 5002 |

    | ss_dinfo_tagname | 0.01562500 | 0.00000000 | 0.01562500 | 18 |

    | ss_dinfo_testinfo | 0.01562500 | 0.00000000 | 0.01562500 | 21 |

    | ss_dinfo_video | 0.91273117 | 0.00781250 | 0.92054367 | 207 |

    | ss_djiao | 0.01562500 | 0.00000000 | 0.01562500 | 3 |

    | ss_dliberty | 3.79615784 | 0.27734375 | 4.07350159 | 23108 |

    | ss_dliberty_item | 3.08350754 | 1.24414063 | 4.32764816 | 51113 |

    | ss_dloan | 0.00000000 | 0.00390625 | 0.00390625 | 0 |

    | ss_dmarke | 0.01973343 | 0.00390625 | 0.02363968 | 125 |

    | ss_dmedal | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_dmember_comment | 0.00995636 | 0.00195313 | 0.01190948 | 87 |

    | ss_dmembers | 6.73762321 | 0.33203125 | 7.06965446 | 30717 |

    | ss_dmembers_identity | 0.10503769 | 0.00976563 | 0.11480331 | 784 |

    | ss_dnewyear | 0.00308609 | 0.00195313 | 0.00503922 | 32 |

    | ss_drecharge | 29.13045883 | 10.23925781 | 39.36971664 | 221220 |

    | ss_dsalon_click | 0.00586700 | 0.00195313 | 0.00782013 | 31 |

    | ss_dsalon_comment | 0.07812500 | 0.00000000 | 0.07812500 | 213 |

    | ss_dsalon_content | 0.14062500 | 0.00000000 | 0.14062500 | 653 |

    | ss_dsalon_ticket | 0.00093079 | 0.00195313 | 0.00288391 | 20 |

    | ss_dsignin | 0.37500000 | 0.00000000 | 0.37500000 | 7177 |

    | ss_dtemplate | 0.15721893 | 0.03808594 | 0.19530487 | 2838 |

    | ss_dwd_answers | 2.51562500 | 0.00000000 | 2.51562500 | 5420 |

    | ss_dwd_classify | 0.01562500 | 0.00000000 | 0.01562500 | 12 |

    | ss_dwd_collection | 0.01562500 | 0.00000000 | 0.01562500 | 2 |

    | ss_dwd_questions | 1.51562500 | 0.00000000 | 1.51562500 | 3614 |

    | ss_dwget | 1894.69999695 | 13.02929688 | 1907.72929382 | 1187574 |

    | ss_dzhuanti_bolanhui | 0.00419235 | 0.00195313 | 0.00614548 | 20 |

    | click | 0.00000000 | 0.00097656 | 0.00097656 | 0 |

    | ss_account | 0.14062500 | 0.07812500 | 0.21875000 | 1871 |

    +---------------------------+---------------+-------------+---------------+------------+

    展开全文
  • use information_schema; SELECT TABLE_NAME, (DATA_LENGTH/1024/1024) as DataM , (INDEX_LENGTH/1024/1024) as IndexM, ... ((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllM, ... ...
    use information_schema;  
    SELECT   
       TABLE_NAME,  
        (DATA_LENGTH/1024/1024) as DataM ,  
        (INDEX_LENGTH/1024/1024) as IndexM,   
        ((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as AllM,  
        TABLE_ROWS  
    FROM  
        TABLES  
    WHERE  
        TABLE_SCHEMA = 'dbname' order by DataM desc;
    

     

    转载于:https://www.cnblogs.com/lixx/p/8253148.html

    展开全文
  • --统计某个的空间大小,行数信息EXECYour_DB.dbo.sp_...--统计多个数据库多个的空间大小,行数信息createtabletmp_table_space(table_namevarchar(50),table_rowsint,total_sizevarchar(20),data_sizevarchar(20),...

    -- 统计某个表的空间大小, 行数信息

    EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log';

    -- 统计多个数据库多个表的空间大小, 行数信息

    create table tmp_table_space(table_name varchar(50), table_rows int, total_size varchar(20),data_size varchar(20), index_size varchar(20),

    unused_size varchar(20), dbname varchar(30));

    insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size)

    EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log';

    insert into tmp_table_space(table_name,table_rows,total_size,data_size,index_size,unused_size)

    EXEC Your_DB.dbo.sp_spaceused 'dbo.t_log2';

    update tmp_table_space set dbname='AHBZMJ' where dbname is null;

    /*************************************************************************

    用于查看对应数据库的大小、占用空间以及该数据库中各个系统表、用户表

    使用方法:  在查询分析器中选择您要查看的数据库,然后运行此代码即可。

    **************************************************************************/

    ----新建一个表spt_result_table存储数据库中各个表的空间信息

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]')

    and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[spt_result_table]

    GO

    create table spt_result_table

    (

    tablename       varchar(776)      null,  ----表名

    rows varchar(776)      null,  ----表中现有的行数

    reserved varchar(776)      null,  ----为表保留的空间总量

    data varchar(776)      null,  ----表中的数据所使用的空间量

    indexp varchar(776)      null,  ----表中的索引所使用的空间量

    unused varchar(776)      null   ----表中未用的空间量

    )ON [PRIMARY]

    GO

    ----创建存储过程prc_database_spaceused:计算数据库大小及各个表占用空间的情况

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]')

    and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[prc_database_spaceused]

    GO

    create procedure prc_database_spaceused

    as

    BEGIN

    declare @id                 int

    declare @type         character(2)

    declare @pages         int

    declare @dbname             sysname           ----数据库名

    declare @dbsize             dec(15,0)         ----数据库大小

    declare @logsize            dec(15)

    declare @bytesperpage dec(15,0)

    declare @pagesperMB dec(15,0)

    declare @objname            varchar(776)       ----记录表名

    declare @database_size       varchar(776)

    declare @unallocated_space   varchar(776)

    select  @dbname = db_name()                   ----数据库为当前数据库

    create table #spt_space

    (

    rows int     null,

    reserved dec(15) null,         ----保留的空间总量

    data dec(15) null,         ----数据使用的空间总量

    indexp dec(15) null,         ----索引使用的空间

    unused dec(15) null          ----未用的空间量

    )

    ---- 计算数据大小(以kB页为单位)

    select  @dbsize = sum(convert(dec(15),size))

    from    dbo.sysfiles

    where   (status & 64 = 0)

    ---- 计算日志大小(以kB页为单位)

    select  @logsize = sum(convert(dec(15),size))

    from    dbo.sysfiles

    where   (status & 64 <> 0)

    ---- 求得一个page有多少bytes

    select  @bytesperpage = low

    from    master.dbo.spt_values

    where   number = 1 and type = 'E'

    ---- 计算MB占多少page(MB = 1048576B)

    select  @pagesperMB = 1048576 / @bytesperpage

    ---- 计算数据库大小

    set  @database_size   = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB')

    ---- 计算未用的空间量的大小

    set     @unallocated_space   = ltrim(str((@dbsize -

    (select sum(convert(dec(15),reserved))

    from sysindexes

    where indid in (0, 1, 255)

    )) / @pagesperMB,15,2)+ ' MB')

    ---- 保留的空间总量

    insert into #spt_space (reserved)

    select sum(convert(dec(15),reserved))

    from   sysindexes

    where  indid in (0, 1, 255)

    select @pages = sum(convert(dec(15),dpages))

    from   sysindexes

    where  indid 

    select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)

    from   sysindexes

    where  indid = 255

    ---- 数据使用的空间总量

    update #spt_space

    set    data = @pages

    ---- 索引使用的空间

    update #spt_space

    set    indexp = (select sum(convert(dec(15),used))

    from   sysindexes

    where  indid in (0, 1, 255))- data

    ---- 未用的空间量

    update #spt_space

    set    unused = reserved - (select sum(convert(dec(15),used))

    from   sysindexes

    where  indid in (0, 1, 255))

    ---- 输出数据库大小信息

    select

    database_name     = @dbname,

    database_size     = @database_size,

    unallocated_space = @unallocated_space,

    reserved          = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

    data              = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

    index_size        = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

    unused            = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

    from   #spt_space, master.dbo.spt_values d

    where  d.number = 1

    and    d.type = 'E'

    ---- 清空临时表#spt_space

    delete from  #spt_space

    -----定义游标,计算表大小信息

    declare  cur_table  cursor for

    select   name

    from     sysobjects

    where    xtype = 'U' or xtype = 'S'

    order    by  xtype  asc

    -----打开游标

    open     cur_table

    fetch    next from cur_table   into  @objname

    while    (@@fetch_status = 0)

    begin

    select @id = null

    select @id = id,

    @type = xtype

    from   sysobjects

    where  id = object_id(@objname)

    --dbcc updateusage(0,@objname) with no_infomsgs

    insert into #spt_space (reserved)

    select sum(reserved)

    from   sysindexes

    where  indid in (0, 1, 255)

    and    id = @id

    select @pages = sum(dpages)

    from   sysindexes

    where  indid 

    and    id = @id

    select @pages = @pages + isnull(sum(used), 0)

    from   sysindexes

    where  indid = 255

    and    id = @id

    update #spt_space

    set    data = @pages

    update #spt_space

    set    indexp = (select sum(used)

    from   sysindexes

    where  indid in (0, 1, 255)

    and    id = @id) - data

    update #spt_space

    set    unused = reserved - (select sum(used)

    from   sysindexes

    where  indid in (0, 1, 255)

    and    id = @id)

    update #spt_space

    set    rows = i.rows

    from   sysindexes i

    where  i.indid 

    and    i.id = @id

    insert into spt_result_table

    select tablename = object_name(@id),

    rows = convert(char(11), rows),

    reserved = ltrim(str(reserved * d.low / 1024.,15,0) +  ' ' + 'KB'),

    data = ltrim(str(data * d.low / 1024.,15,0) +  ' ' + 'KB'),

    index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

    unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

    from   #spt_space, master.dbo.spt_values d

    where  d.number = 1

    and    d.type = 'E'

    truncate table  #spt_space

    fetch   next from cur_table   into @objname

    end

    close        cur_table

    deallocate   cur_table

    select * from spt_result_table where tablename is not null;

    drop table #spt_space

    END

    GO

    exec prc_database_spaceused

    ---- 删除spt_result_table表

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spt_result_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[spt_result_table]

    GO

    ---- 删除存储过程prc_database_spaceused

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prc_database_spaceused]')

    and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[prc_database_spaceused]

    GO

    展开全文
  • 统计 MySQL 数据库中每个数据与索引占用的空间大小
  • 查询数据库表大小 查看数据库列表 查询所有数据库的总大小 统计所有库数据量 统计每个库大小 查看指定数据库的大小 查看所有数据库各个容量大小 查看所有数据库各个表容量大小 查看指定数据库容量大小 查看指定...
  • 本文介绍如何查看MySQL数据库或数据的数据容量大小。1.查询所有数据库记录数和容量SELECTtable_schemaAS'数据库',SUM(table_rows)AS'记录数',SUM(TRUNCATE(data_length/1024/1024,2))AS'数据容量(MB)',SUM...
  • Mysql 统计数据库数据大小 当我们需要知道数据库占用了多少磁盘大小,可以通过一些sql语句查询到整个数据库的容量,也可以单独查看所占容量。 # 根据条件查询,打印出查询的的数据大小(逐个打印信息) ...
  • mysql数据库表统计大小

    千次阅读 2015-04-08 17:24:45
    单位为字节B•查看各库的大小SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables WHERE TABLE_...•查看各大小 SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM informa
  • 三、DM(达梦数据库):1、首先使用存储过程执行特定用户的表统计行数,否则NUM_ROWS为nullDBMS_STATS.GATHER_SCHEMA_STATS('ROOT',100,TRUE,'FORALLTABLENUM_ROWSAUTO');2、使用和oracle一样的sql统计行数和数据量...
  • 统计MySQL数据表大小

    2020-03-16 20:56:56
    有时候需要查询MySQL数据库中各个表大小,该如何操作呢? MySQL中有一个名为 information_schema 的数据库,在该库中有一个 TABLES 表,这个表主要字段分别是: TABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ENGINE...
  • 工作中遇到 需要统计MySQL数据库的存储内存大小,这篇博客亲测可用,因此转载以做收藏,本文转自 https://blog.csdn.net/chen3888015/article/details/79665568 查询各个数据库占用空间大小 select TABLE_...
  • 作用:统计每个库的真实数据与索引数据的总和的大小单位为MB,真实数据的大小单位MB,索引数据的大小MB,含有的总数,当前日期。并且以每个库的大小倒序排序。   SELECT table_schema,\ SUM(data_length+index...
  • SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',  CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024),4),'MB') AS 'Data Size',  ...
  • 1. 查看该数据库实例下所有库大小,得到的结果是以MB为单位mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 \as index_length,sum(data_length+index_length...
  • information_schema库中存储了MySQL数据库统计信息,可以通过它来获取相关信息。 1. 查看某个数据库大小 如果只看数据大小,可以不加索引大小 sum(INDEX_LENGTH/1024/1024),1) select concat(round(sum(DATA_...
  • 查看mysql数据库大小的四种办法,分别有以下四种:第一种:进去指定schema 数据库(存放了其他的数据库的信息)use information_schema第二种:查询所有数据的大小select concat(round(sum(DATA_LENGTH/1024/1024),2...
  • Mysql数据库空间占用信息统计

    千次阅读 2018-12-05 15:42:35
    1、mysql中查看各大小 这里用到一个, information_schema.tables;对应主要字段含义如下: ABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ENGINE:所使用的存储引擎 TABLES_ROWS:记录数 DATA_LENGTH:数据大小 ...
  • 这里用到一个, information_schema.tables;对应主要字段含义如下: TABLE_SCHEMA : 数据库名 TABLE_NAME:表名 ENGINE:所使用的存储引擎 ...1.查询数据库,行数统计记录 select table_schema,table_...
  • 统计指定数据库大小SELECT TABLE_SCHEMA ,CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M') AS 'DATA_LENGTH_MB' ,CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'M') AS 'INDEX_LENGTH_MB' ,CONCAT(ROUND(SUM(DATA...
  • 第一种情况:查询所有数据库的总大小,方法如下: mysql> use information_schema; mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES; +-----------+ | data | +--------...
  • -- 查询所有数据库容量大小 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 '索引容量...
  • 满意答案a7050738932018.06.23采纳率:41%等级:12已帮助:7760人查看mysql数据库大小的四种办法,分别有以下四种:第一种:进去指定schema 数据库(存放了其他的数据库的信息)use information_schema第二种:查询...
  • 第一种情况:查询所有数据库的总大小 方法如下: mysql> use information_schema; mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES; +-----------+ | data | +---------...
  • MySQL统计库或表大小

    2018-07-17 17:21:20
    mysql查看当前所有的数据库和索引大小select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_sizefrom ...
  • 概述前面已经介绍了Oracle如何去获取某用户下的所有的行数,所以就不介绍了,今天主要分享的是怎么去获取mysql数据库下所有的数据行数,主要介绍3个方法。1、估算某个数据库下的所有的记录数可以用MySQL 自带...
  • 今天主要复习了MySQL的一些简单语句,可总结:mysql 统计 表的数量...有时候需要查询MySQL数据库中各个表大小,该如何操作呢?MySQL中有一个名为 information_schema 的数据库,在该库中有一个 TABLES 表,这个表主...

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 145
精华内容 58
关键字:

统计mysql数据库表大小

mysql 订阅