查看数据库中表占用的空间

最新推荐文章于 2024-07-05 14:36:55 发布

Blessed_Chuan

最新推荐文章于 2024-07-05 14:36:55 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/gykthh/article/details/8651613

版权

IF NOT EXISTS ( SELECT  *
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[tablespaceinfo]')
                        AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 
    CREATE TABLE tablespaceinfo --创建结果存储表
        (
          nameinfo VARCHAR(50) ,
          rowsinfo INT ,
          reserved VARCHAR(20) ,
          datainfo VARCHAR(20) ,
          index_size VARCHAR(20) ,
          unused VARCHAR(20)
        )


TRUNCATE TABLE tablespaceinfo
 --清空数据表
INSERT  INTO tablespaceinfo
        EXEC sp_MSforeachtable "exec sp_spaceused '?'" 
go


SELECT  nameinfo ,
        rowsinfo ,
        CAST(CAST(REPLACE(reserved, 'KB', '') AS FLOAT) / 1024 AS VARCHAR)
        + 'MB' AS reserved ,
        CAST(CAST(REPLACE(datainfo, 'KB', '') AS FLOAT) / 1024 AS VARCHAR)
        + 'MB' AS datainfo ,
        index_size ,
        unused
FROM    tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC