SqlServer_小工具_获取数据库空间信息获取

获取磁盘空间占用情况
exec sp_spaceused

获取指定对象占用空间情况
exec sp_spaceused 'SyncOpenId'

磁盘可用空间
Exec master.dbo.xp_fixeddrives

日志空间
DBCC SQLPERF(LOGSPACE)


CREATE TABLE #Data
    (
      name VARCHAR(100) ,
      row VARCHAR(100) ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    ); 
 
DECLARE @name VARCHAR(100); 
DECLARE cur CURSOR
FOR
    SELECT  name
    FROM    sysobjects
    WHERE   xtype = 'u'
    ORDER BY name; 
OPEN cur; 
FETCH NEXT FROM cur INTO @name; 
WHILE @@fetch_status = 0
    BEGIN 
        INSERT  INTO #Data
                EXEC sp_spaceused @name; 
        PRINT @name; 
 
        FETCH NEXT FROM cur INTO @name; 
    END; 
CLOSE cur; 
DEALLOCATE cur; 
 
CREATE TABLE #DataNew
    (
      name VARCHAR(100) ,
      row INT ,
      reserved INT ,
      data INT ,
      index_size INT ,
      unused INT
    ); 
 
INSERT  INTO #DataNew
        SELECT  name ,
                CONVERT(INT, row) AS row ,
                CONVERT(INT, REPLACE(reserved, 'KB', '')) AS reserved ,
                CONVERT(INT, REPLACE(data, 'KB', '')) AS data ,
                CONVERT(INT, REPLACE(index_size, 'KB', '')) AS index_size ,
                CONVERT(INT, REPLACE(unused, 'KB', '')) AS unused
        FROM    #Data;  

SELECT  name 表名 ,
        row 记录行数 ,
        dbo.FitSpace(reserved) 占用大小 ,
        dbo.FitSpace(data) 数据大小 ,
        dbo.FitSpace(index_size) 索引大小 ,
        dbo.FitSpace(unused) 未使用大小
FROM    #DataNew
ORDER BY data DESC;

SELECT  dbo.FitSpace(SUM(reserved))
FROM    #DataNew;
--614.616GB
--582.698GB
EXEC sp_spaceused 'SyncOpenId';
 --取得表占用空間 
EXEC sp_spaceused;
607535.88 MB
--所有空间
EXEC master.dbo.xp_fixeddrives;
--可用空间
DBCC SQLPERF(LOGSPACE);--日志空间
--查看数据库文件
SELECT * FROM sys.database_files