SQL Server sp_helpdb 改写

在SQL Server 数据库管理中，我们常使用sp_helpdb 来查看数据库的基本信息，如我们要查看test数据库的信息，可以执行下面脚本：

EXEC sp_helpdb test

查询结果如下：

但结果中无论是db_size 还是文件的size，都是分配的空间，我们不能看出数据库使用的空间有多少，未使用空间有多少。这个数据库的使用空间对前文中数据库收缩有很大的用处。在最开始我是通过SSMS对象资源管理器，右击目标数据库，在任务→收缩→文件中查看数据库或者数据库文件的空间使用情况的，如下图：

这样操作对于高逼格的管理员来说，显得就比较low了。当然最主要的是重复性操作性低。为此查看系统视图 sysfiles、database_files，系统过程 sp_helpfile 均只记录数据库的分配空间大小。使用前文中提到的 dbcc showfilestats，发现其只有数据文件的空间分配使用情况。最终找到了fileProperty内置函数，使用其 usedSpace 参数，结合系统视图、获取到了所有数据库文件空间分配使用情况。为满足高效管理的需要，我将sp_helpdb 改写为 sp_DBA_helpdb，具体脚本如下：

USE [master]
GO
/*
      数据库基本信息查看
      完善系统过程sp_helpdb
      --exec sp_DBA_helpdb 'master'
      create by Jack
*/
CREATE PROCEDURE [dbo].[sp_DBA_helpdb] @dbname SYSNAME = NULL   -- 数据库名
AS
BEGIN
DECLARE @exec_stmt NVARCHAR(625) 
DECLARE @showdev BIT 
DECLARE @name SYSNAME 
DECLARE @cmd NVARCHAR(285) -- (26 + 258) + 1 extra 
DECLARE @dbdesc VARCHAR(600) /* 数据库的属性信息*/ 
DECLARE @propdesc VARCHAR(40) 
 
SET nocount ON 
CREATE TABLE #spdbdesc
    (
      dbName SYSNAME
      ,owner SYSNAME NULL
      ,created NVARCHAR(11)
      ,dbid SMALLINT
      ,dbdesc NVARCHAR(600) NULL
      ,dbTotalSize NVARCHAR(13) NULL  --数据库分配空间（总空间）
      ,dbUsedSize NVARCHAR(13) NULL       --数据库已经使用的空间
      ,dbFreeSizeRatio NVARCHAR(15) NULL  --数据库可用空间比例
      ,cmptLevel TINYINT  --兼容级别
    ) 
/* 
**  如果指定数据库名称为null，返回实例上所有数据库信息，
**  否则返回对应数据库信息及数据库的文件信息
*/ 
IF @dbname IS NULL
    SELECT  @showdev = 0 
ELSE
    SELECT  @showdev = 1 
 
/* 
**  检验数据库是否存在
*/ 
IF NOT EXISTS ( SELECT  *
                FROM    master.dbo.sysdatabases
                WHERE   ( @dbname IS NULL
                          OR name = @dbname
                        ) )
    BEGIN 
        RAISERROR(15010,-1,-1,@dbname) 
        RETURN (1) 
    END 
 
 
/* 
**  从系统视图sysdatabases 初始化#spdbdesc 表
*/ 
INSERT  INTO #spdbdesc ( dbname, owner, created, dbid, cmptlevel )
        SELECT  name, ISNULL(SUSER_SNAME(sid), '~~UNKNOWN~~')
                  , CONVERT(NVARCHAR(11), crdate), dbid, cmptlevel
        FROM    master.dbo.sysdatabases
        WHERE   ( @dbname IS NULL
                  OR name = @dbname
                )  --如果指定数据库名称为null，则返回所有实例上所有数据库信息
 
/*
      **fileProperty 只能获取当前会话的文件属性，否则返回NULL
      **下面将循环每个库的每个文件，获取已经使用的文件大小
*/
IF OBJECT_ID('tempdb..#fileSize') IS NOT NULL
    DROP TABLE #fileSize
CREATE TABLE #fileSize
    (
      dbName VARCHAR(256) ,
      fName VARCHAR(256) ,
      totalSize BIGINT ,
      usedSize BIGINT
    )
 
DECLARE ms_crs_c1 CURSOR global
FOR
SELECT  DB_NAME(dbid)
FROM    #spdbdesc 
OPEN ms_crs_c1 
FETCH ms_crs_c1 INTO @name 
WHILE @@fetch_status >= 0
    BEGIN 
        IF ( HAS_DBACCESS(@name) <> 1 )  --判断是否有权限访问数据库，不等于无权限访问
            BEGIN 
                DELETE  #spdbdesc
                WHERE CURRENT OF ms_crs_c1 
                RAISERROR(15622,-1,-1, @name) 
            END 
        ELSE
            BEGIN 
                IF OBJECT_ID('tempdb..#fileName') IS NOT NULL
                    DROP TABLE #fileName
                CREATE TABLE #fileName
                    (
                      name VARCHAR(256) ,
                      TotalSize BIGINT
                    )
                SET @exec_stmt = 'select name,size from ' + QUOTENAME(@name) + '.sys.sysfiles'
                INSERT  INTO #fileName ( name, TotalSize )
                        EXEC ( @exec_stmt
                            )
                DECLARE @fname VARCHAR(256) ,
                    @TotalSize BIGINT
                DECLARE fName CURSOR
                FOR
                SELECT  name, TotalSize
                FROM    #fileName
                OPEN fName
                FETCH NEXT FROM fName INTO @fName, @TotalSize
                WHILE @@FETCH_STATUS >= 0
                    BEGIN
                        SET @exec_stmt = 'use ' + QUOTENAME(@name) + ' select fileProperty(''' + @fname
                            + ''',''SpaceUsed'')'
                        INSERT  INTO #fileSize ( usedSize )
                                EXEC ( @exec_stmt)
                        UPDATE  #fileSize
                        SET     dbName = @name, fName = @fname, totalSize = @TotalSize
                        WHERE   dbName IS NULL
                        FETCH NEXT FROM fName INTO @fName, @TotalSize
                    END
                CLOSE fName
                DEALLOCATE fName
       
            END 
        FETCH ms_crs_c1 INTO @name 
    END 
DEALLOCATE ms_crs_c1;
WITH    cte
          AS (
               SELECT   dbName, SUM(totalSize) totalSize, SUM(usedSize) usedSize
               FROM     #fileSize
               GROUP BY dbName
             )
UPDATE  #spdbdesc
SET     dbTotalSize = cte.totalSize, dbUsedSize = cte.usedSize
FROM    #spdbdesc s
LEFT JOIN cte ON cte.dbName = s.dbName
UPDATE  #spdbdesc
SET     dbFreeSizeRatio = STR(( CONVERT(dec(17, 2), dbTotalSize) - CONVERT(dec(17, 2), dbUsedSize) ) * 100.0
                              / CONVERT(dec(17, 2), dbTotalSize), 5, 2) + '%'
UPDATE  #spdbdesc
SET     dbTotalSize = STR(CONVERT(DECIMAL(17,2), dbTotalSize)/128.0,10,2) + ' MB'
      , dbUsedSize = STR(CONVERT(DECIMAL(17,2), dbUsedSize)/128.0,10,2) + ' MB'
/* 
**  Now for each dbid in #spdbdesc, build the database status 
**  description. 
*/ 
DECLARE @curdbid SMALLINT /* the one we're currently working on */ 
/* 
**  Set @curdbid to the first dbid. 
*/ 
SELECT  @curdbid = MIN(dbid)
FROM    #spdbdesc 
 
 
WHILE @curdbid IS NOT NULL
    BEGIN 
        SET @name = DB_NAME(@curdbid) 
 
 -- These properties always available 
        SELECT  @dbdesc = 'Status=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'Status')) 
        SELECT  @dbdesc = @dbdesc + ', Updateability=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'Updateability')) 
        SELECT  @dbdesc = @dbdesc + ', UserAccess=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'UserAccess')) 
        SELECT  @dbdesc = @dbdesc + ', Recovery=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'Recovery')) 
        SELECT  @dbdesc = @dbdesc + ', Version=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'Version')) 
 
 -- These props only available if db not shutdown 
        IF DATABASEPROPERTY(@name, 'IsShutdown') = 0
            BEGIN 
                SELECT  @dbdesc = @dbdesc + ', Collation=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name, 'Collation')) 
                SELECT  @dbdesc = @dbdesc + ', SQLSortOrder=' + CONVERT(SYSNAME, DATABASEPROPERTYEX(@name,
                                                                                                    'SQLSortOrder')) 
            END 
 
 -- These are the boolean properties 
        IF DATABASEPROPERTYEX(@name, 'IsAutoClose') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAutoClose' 
        IF DATABASEPROPERTYEX(@name, 'IsAutoShrink') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAutoShrink' 
        IF DATABASEPROPERTYEX(@name, 'IsInStandby') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsInStandby' 
        IF DATABASEPROPERTYEX(@name, 'IsTornPageDetectionEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsAnsiNullDefault') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault' 
        IF DATABASEPROPERTYEX(@name, 'IsAnsiNullsEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsAnsiPaddingEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsAnsiWarningsEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsArithmeticAbortEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsAutoCreateStatistics') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics' 
        IF DATABASEPROPERTYEX(@name, 'IsAutoUpdateStatistics') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics' 
        IF DATABASEPROPERTYEX(@name, 'IsCloseCursorsOnCommitEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsFullTextEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsLocalCursorsDefault') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault' 
        IF DATABASEPROPERTYEX(@name, 'IsNullConcat') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsNullConcat' 
        IF DATABASEPROPERTYEX(@name, 'IsNumericRoundAbortEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsQuotedIdentifiersEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsRecursiveTriggersEnabled') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled' 
        IF DATABASEPROPERTYEX(@name, 'IsMergePublished') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsMergePublished' 
        IF DATABASEPROPERTYEX(@name, 'IsPublished') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsPublished' 
        IF DATABASEPROPERTYEX(@name, 'IsSubscribed') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsSubscribed' 
        IF DATABASEPROPERTYEX(@name, 'IsSyncWithBackup') = 1
            SELECT  @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup' 
 
        UPDATE  #spdbdesc
        SET     dbdesc = @dbdesc
        WHERE   dbid = @curdbid 
 
 /* 
 **  Now get the next, if any dbid. 
 */ 
        SELECT  @curdbid = MIN(dbid)
        FROM    #spdbdesc
        WHERE   dbid > @curdbid 
    END 
 
/* 
**  Now #spdbdesc is complete so we can print out the db info 
*/ 
SELECT  name = dbname, dbTotalsize, dbUsedSize, dbFreeSizeRatio, owner = owner, dbid = dbid, created = created,
        status = dbdesc, compatibility_level = cmptlevel
FROM    #spdbdesc
ORDER BY dbname 
 
/* 
**  If we are looking at one database, show its file allocation. 
*/ 
IF @showdev = 1
    AND HAS_DBACCESS(@dbname) = 1
    BEGIN 
        PRINT N' ' 
        IF OBJECT_ID('tempdb..#fileInfo') IS NOT NULL
            DROP TABLE #fileInfo
        CREATE TABLE #fileInfo
            (
              Name VARCHAR(256) ,
              Fileid SMALLINT ,
              filename VARCHAR(256) ,
              FileGroup VARCHAR(520) ,
              TotalSize NVARCHAR(13) ,
              UsedSize NVARCHAR(13) ,
              FreeSizeRatio NVARCHAR(15) ,
              maxsize VARCHAR(20) ,
              growth VARCHAR(15) ,
              usage VARCHAR(20)
            )
        SELECT  @cmd = N'use ' + QUOTENAME(@dbname) + N' exec sys.sp_helpfile' 
        INSERT  INTO #fileInfo ( Name, Fileid, filename, FileGroup, TotalSize, maxsize, growth, usage )
                EXEC ( @cmd
                    )
       
        UPDATE  #fileInfo
        SET     TotalSize = STR(CONVERT(dec(17, 2), LEFT(TotalSize, LEN(TotalSize) - 2)) / 1024.0, 10, 2)
        UPDATE  #fileInfo
        SET     TotalSize = STR(f.TotalSize, 10, 2) + ' MB'
                        , UsedSize = STR(CONVERT(dec(17, 2),s.usedSize)/128.0, 10, 2) + ' MB',
                FreeSizeRatio = STR(( CONVERT(dec(17, 2), s.TotalSize) - CONVERT(dec(17, 2), s.usedSize) ) * 100.0
                                    / CONVERT(dec(17, 2), s.TotalSize), 5, 2) + '%'
        FROM    #fileInfo f
        LEFT JOIN #fileSize s ON f.Name = s.fName
                                 AND s.dbName = @dbname
        SELECT  Name, Fileid, filename, FileGroup, TotalSize, UsedSize, FreeSizeRatio, maxsize, growth, usage
        FROM    #fileInfo
 
    END 
RETURN (0) -- sp_helpdb 
END
EXEC sys.sp_MS_marksystemobject 'sp_DBA_helpdb';

对改写后的sp_DBA_helpdb进行测试，首先传入参数NULL，如下：

EXEC sp_DBA_helpdb NULL

然后在将参数换为指定的数据库，这里使用的是test库：

EXEC sp_DBA_helpdb test

从测试的结果来看，完全符合我们的预期。

这样当我们使用前文中的sp_DBA_shrikfile对单个数据库文件进行收缩时，sp_DBA_helpdb能够快速为我们确定各参数的值。同时sp_DBA_helpdb亦能帮助我们快速确定哪些文件需要收缩、收缩哪些文件可以快速释放大量空间。

后记

这里我直接改了sp_helpdb来实现目标，当然也可以先修改sp_helpfile，实现目标后再调用修改后的sp_helpfile 修改sp_DBA_helpdb。 

​如果想更为及时的获取最新文章，可以搜索注公众 MSQLServer，将有更多精彩。