Novick Software
SQL Server Consulting • Design • Programming • Tuning

  andy novick is a sql server mvp

 

 

Table Space Reporting with dba_spaceused

Andrew Novick, June, 2007                       

Managing space is a constant responsibility for the DBA.  When space becomes a problem I often want to know the size of each table. It isn’t difficult to find the amount of space used by a table, SQL Server provides the sp_spaceused stored procedure, which can report on the space used for a table or for the database as a whole.  A script can execute sp_spaced used once for each table and collect the results into a single result set so it’s more convenient for reporting.  The scripts that I’ve seen on other sites have done that but don’t report on the schema name.  This article presents a stored procedure, dba_spaceused, that does a particularly good job of reporting the space used by each table in a convenient way while including schema information in the results.

 I use to advocate assigning ownership of all tables to dbo.  This removed the possible duplication of table names between owners and life was good.  SQL Server 2005 has a new schema object that owns tables, views, and other database objects. Since schemas can now be created without creating a user a whole level of confusion that existed when users owned tables is gone.  I find that I’m now using schemas to organize my databases. With named schemas owning tables instead of dbo the inevitable result is multiple tables with the same name owned by more than one schema.  Thus my space reporting code must account for the schema name.

Dba_spaceused uses sp_spaceused to gather information about all tables in the database.  As it captures them it stores the schema name along with the table name in the #TabSpace table for later reporting.  The results are reported from #TabSpace sorted the way the caller requested, by size, name, which includes the schema name, or table name, which ignores the schema name.  Here’s the code:

 

    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 

There are two parameters to dba_spaceused.  The first parameter is the name of the database to report on.  Use of this parameter allows is sp to be stored in any database on the server.  It’s not necessary to keep a copy in every database.  I keep it in my “DBA” database.  If the database name is null, the current database is assumed.  The second parameter is the choice of sort order.  The choices are:

S

Reserved space for the table

N

Schema name then table name

T

Table name without regard to schema

You are free to use dba_spaceused and copy it into your projects at your own risk so long as you keep the copyright notice intact.

About the Author

Andrew Novick is Principal of Novick Software where he develops business applications as a consultant using ASP/ASP.NET, VB/VB .NET, XML and SQL Server. He is the author of Transact-SQL User Defined Functions. 
2007 marks his 36th year of computer programming, starting in High School with a PDP-8 and moving onto a degree in Computer Science, an MBA, and then programming mainframes, minicomputers, and for the last 20 years, PCs. When not programming, he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife. He can be reached at anovick@NovickSoftware.com.


RSS as HTML

Personal Blog
 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage
Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition

Full Schedule