각 데이터베이스의 사용량 확인

dba_spaceused.zip

http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm

    1 CREATE PROC [dbo].[dba_SpaceUsed] 

    2 

    3     @SourceDB varchar ( 128 ) = null -- Optional database name

    4          -- If omitted, the current database is reported.

    5   , @SortBy char(1) = 'S' -- N for name, S for Size

    6            -- T for table name

    7 

    8 /* Returns a table with the space used in all tables of the

    9 *  database.  It's reported with the schema information unlike

   10 *  the system procedure sp_spaceuse.

   11 *

   12 *  sp_spaceused is used to perform the calculations to ensure

   13 *  that the numbers match what SQL Server would report.

   14 *

   15 *  Compatible with sQL Server 2000 and 2005

   16 *

   17 * Example:

   18 exec dbo.dba_SpaceUsed null, 'T'

   19 *

   20 * © Copyright 2007 Andrew Novick http://www.NovickSoftware.com

   21 * This software is provided as is without warrentee of any kind.

   22 * You may use this procedure in any of your SQL Server databases

   23 * including databases that you sell, so long as they contain 

   24 * other unrelated database objects. You may not publish this 

   25 * procedure either in print or electronically.

   26 ******************************************************************/

   27 AS

   28 

   29 SET NOCOUNT ON

   30 

   31 DECLARE @sql nvarchar (4000)

   32 

   33 IF @SourceDB IS NULL BEGIN

   34     SET @SourceDB = DB_NAME () -- The current DB 

   35 END

   36 

   37 --------------------------------------------------------

   38 -- Create and fill a list of the tables in the database.

   39 

   40 CREATE TABLE #Tables (    [schema] sysname

   41                       , TabName sysname )

   42 

   43 SELECT @sql = 'insert #tables ([schema], [TabName]) 

   44                   select TABLE_SCHEMA, TABLE_NAME 

   45                   from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES

   46                       where TABLE_TYPE = ''BASE TABLE'''

   47 EXEC (@sql)

   48 

   49 

   50 ---------------------------------------------------------------

   51 -- #TabSpaceTxt Holds the results of sp_spaceused. 

   52 -- It Doesn't have Schema Info!

   53 CREATE TABLE #TabSpaceTxt (

   54                          TabName sysname

   55                        , [Rows] varchar (11)

   56                        , Reserved varchar (18)

   57                        , Data varchar (18)

   58                        , Index_Size varchar ( 18 )

   59                        , Unused varchar ( 18 )

   60                        )

   61 

   62 ---------------------------------------------------------------

   63 -- The result table, with numeric results and Schema name.

   64 CREATE TABLE #TabSpace ( [Schema] sysname

   65                        , TabName sysname

   66                        , [Rows] bigint

   67                        , ReservedMB numeric(18,3)

   68                        , DataMB numeric(18,3)

   69                        , Index_SizeMB numeric(18,3)

   70                        , UnusedMB numeric(18,3)

   71                        )

   72 

   73 DECLARE @Tab sysname -- table name

   74       , @Sch sysname -- owner,schema

   75 

   76 DECLARE TableCursor CURSOR FOR

   77     SELECT [SCHEMA], TabNAME 

   78          FROM #tables

   79 

   80 OPEN TableCursor;

   81 FETCH TableCursor into @Sch, @Tab;

   82 

   83 WHILE @@FETCH_STATUS = 0 BEGIN

   84 

   85     SELECT @sql = 'exec [' + @SourceDB 

   86        + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '

   87        + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';

   88 

   89     Delete from #TabSpaceTxt; -- Stores 1 result at a time

   90     EXEC (@sql);

   91 

   92     INSERT INTO #TabSpace

   93     SELECT @Sch

   94          , [TabName]

   95          , convert(bigint, rows)

   96          , convert(numeric(18,3), convert(numeric(18,3), 

   97                 left(reserved, len(reserved)-3)) / 1024.0) 

   98                 ReservedMB

   99          , convert(numeric(18,3), convert(numeric(18,3), 

  100                 left(data, len(data)-3)) / 1024.0) DataMB

  101          , convert(numeric(18,3), convert(numeric(18,3), 

  102                 left(index_size, len(index_size)-3)) / 1024.0) 

  103                  Index_SizeMB

  104          , convert(numeric(18,3), convert(numeric(18,3), 

  105                 left(unused, len([Unused])-3)) / 1024.0) 

  106                 [UnusedMB]

  107         FROM #TabSpaceTxt;

  108 

  109     FETCH TableCursor into @Sch, @Tab;

  110 END;

  111 

  112 CLOSE TableCursor;

  113 DEALLOCATE TableCursor;

  114 

  115 -----------------------------------------------------

  116 -- Caller specifies sort, Default is size

  117 IF @SortBy = 'N' -- Use Schema then Table Name

  118     SELECT * FROM #TabSpace

  119        ORDER BY [Schema] asc, [TabName] asc

  120 ELSE IF @SortBy = 'T'  -- Table name, then schema

  121     SELECT * FROM #TabSpace

  122        ORDER BY [TabName] asc, [Schema] asc

  123 ELSE  -- S, NULL, or whatever get's the default

  124     SELECT * FROM #TabSpace

  125        ORDER BY ReservedMB desc

  126 ;

  127 

  128 DROP TABLE #Tables

  129 DROP TABLE #TabSpaceTxt

  130 DROP TABLE #TabSpace

  131