Find size of SQL Server tables and other objects with stored procedure

So, I created stored procedure sp_SOS, an expanded version of sp_spaceused, which can be used to calculate SQL Server object space and to perform other functions.

With sp_SOS I wanted to retain the core functionality of sp_spaceused -- i.e., the algorithm to sum up data, index and reserved and unused spaces for an object. I knocked out the database sizing part and will come up with a separate stored procedure solely for a database size report.

Click here to download Listing 1: The complete T-SQL definition for sp_SOS.

Sp_SOS takes eight input parameters briefly described in Table 1.

Variable	Data type	Nullable	Default	Default implication
@DbName	sysname	Yes	NULL	Current database
@SchemaName	sysname	Yes	NULL	All schemas
@ObjectName	sysname	Yes	%	Including all objects in "LIKE" clause
@TopClause	nvarchar(20)	Yes	NULL	All objects. Can be "TOP N" or "TOP N PERCENT"
@ObjectType	nvarchar(50)	Yes	NULL	All objects that can be sized. Valid values are S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table) or any combination of them
@ShowInternalTable	nvarchar(3)	Yes	NULL	Includes internal table. The Parent excludes it in size
@OrderBy	nvarchar(100)	Yes	NULL	By object name, can be any size related column. Valid short terms are N(name), R(row), T(total), U(used), I(index), D(data), F(free or unused) and Y(type)
@UpdateUsage	bit	Yes	0	Do not run "DBCC UPDATEUSAGE"

Table 1: Parameter variables for sp_SOS and their characteristics.

I prefer to use a formula-like style that makes it easy to interpret the figure relationships. The formula is "Total(MB) - Unused(MB) == Used(MB) = Index(MB) + Data(MB)", i.e., the used space is the simultaneous result of total minus unused as well as index plus data. The "reserved" column in sp_spaceused is actually equivalent to the total size in sp_SOS. There's a new concept of the internal table in SQL Server 2005 and SQL Server 2008. They are intermediate tables for parent objects to process XML primary indexes, Service Broker queues, fulltext indexes and query notification subscriptions.

When summing up parent objects, the internal tables of type 202 (xml_index_nodes) and 204 (fulltext_catalog_map) should be computed to add to the total size. Because of this, I designed a temporary base object table (##BO) in sp_SOS in two parts. The left half comprises six

More on SQL Server stored procedures: Examples of SQL Server stored procedures and parameters  Stored procedures in SQL Server: A dozen must-have tips How can I take a backup of a SQL Server stored procedure only?

columns representing the daughter objects. The right half is for the parent objects. When a parent owns a daughter object, it shows different schema, schema id, object name and object id. Otherwise, the names and ids are identical. When an internal table is shown, their parent name is included in parenthesis for clear correlation.

The object type initials are consistent with Microsoft SQL Server Books Online. They can be system table (S), user table (U), view (V), service queue (SQ) or internal table (IT). Each type is separated by space(s), comma(s) or semicolon(s). The number or order of the separators does not matter. If you provide characters other than those allowed, sp_SOS will raise an error and quit. And the stored procedure is Unicode-compatible and case-insensitive.

Sp_SOS works on SQL Server 2000, 2005 and 2008. In SQL Server 2000, values reported by sp_SOS may not be updated. The @UpdateUsage parameter can dictate a "DBCC UPDATEUSAGE" pre-run to ensure the values are current in the following report. But be cautious as it could impact performance in large databases. Starting in SQL Server 2005, sp_spaceused always reports correct figures, which makes the DBCC command obsolete.

Here are several typical scenarios to illustrate how to use sp_SOS:

@DbName is the database name where you want to find object space in SQL Server. If it's not supplied, it will use the current database. For example, if you want a quick overview of space taken by all database objects in AdventureWorks database, you may run the T-SQL statement in Listing 2:

USE AdventureWorks;
EXEC dbo.sp_SOS;

Listing 2: Overview of all object space in AdventureWorks database. Note all parameters are by default. The result displays all objects owned by all schemas in alphabetical order.

Both @SchemaName and @ObjectName can take wildcard % as the default. This allows you to sum up a group of objects that bear a similar pattern in names or owner names. While we are still using AdventureWorks as the sample database, we want to list all Sales schema-owned objects that look like "Sales" in the order of used disk space. We don't want to show internal tables yet. I like to run "DBCC UPDATEUSAGE" first to update any incorrect values. The T-SQL statement looks like this in Listing 3.

sp_SOS 'AdventureWorks', 'Sales%', 'Sales%', NULL, ' SQ,;u v ;iT;', 'no',
'U', 1

Listing 3: List objects owned by Sales schema and named like "Sales", in a descending order of used disk space.

Similar to Listing 3, in Listing 4 the command can be run to retrieve the size information for a particular object. Note that the sizes account for the discrepancy between that displayed on the object properties GUI and that returned by either sp_SOS or sp_spaceused. Note, too, that the supplemented parent name is placed behind the XML index to show its affiliation.

sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT',
'yes', 'N', 0

Listing 4: Check space for a specified object. Since it's an internal table, the schema name is ignored. Obviously one object does not need to be ordered, so the @OrderBy is skipped too.

Below are two screenshots for sp_SOS executing in one of my managed SQL Server 2000 databases:

You can see two groups of tables, ARCHIVED_HISTORY and HISTORY sets of tables in Figure 1. I often have to get the grand total of the HISTORY tables. With sp_SOS, I can sum up the values at the bottom of the result. Figure 2 shows the details of DBCC UPDATEUSAGE for each table involved. The arrow (= =>) indicates the actual commands followed by updating details.

Figure 1: sp_SOS shows the grouping of user tables that has similar names and the subtotal space for that group in a SQL Server 2000 user database. (Click on image for enlarged view.)

Figure 2: SQL Server 2000 Query Analyzer shows the sp_SOS snippet and detailed result of "DBCC UPDATEUSAGE" command. In this case, the majority of objects need to update their size information. (Click on image for enlarged view.)

Lastly, here's how sp_SOS works on SQL Server 2008 CTP. Using the script in Listing 5, we obtained the result shown in Figure 3.

EXEC dbo.sp_SOS @DbName = N'AdventureWorks2008', @SchemaName = NULL, @ObjectName = NULL,
@TopClause = NULL, @ObjectType = N'UVITSQ',
@ShowInternalTable = N'Yes', @OrderBy = N'Y', @UpdateUsage = 1

Listing 5: Rank all user tables, views, internal tables and service queues from AdventureWorks2008 database on SQL Server 2008 CTP in the order of object types. Run DBCC UPDATEUSAGE before finalizing the report. Show internal objects as well.

Figure 3: sp_SOS, compatible with SQL Server 2008 CTP, runs in AdventureWorks2008 database to show a list of all objects ordered by object types. (Click on image for enlarged view.)

I can't show you all instances of differently parameterized sp_SOS executions. If you find something interesting or conflicting with its expected behavior, please drop me a line and I'll see if I can improve it.

Check out my follow-up tip on the stored procedure sp_SDS. Not only will sp_SDS determine "SQL Database Space," but it can also be used to monitor database growth, alert a DBA on data or log file growth, execute a transaction log backup and even provide a detailed breakdown at the file level so a DBA can then shrink files with the most empty space.

ABOUT THE AUTHOR:

Richard Ding, database administrator at Northeastern University in Boston, has worked with SQL Server since the late 1990s. His interests cover database administration, T-SQL development, disaster recovery, replication and performance tuning. He writes for several magazines for the SQL Server product, including SQL Server Magazine and SQL Server Standard. He is a freelancer at SearchSQLServer.com and a member of various SQL Server online forums. Despite an earlier degree in medicine and a doctorate in Bio Science, Richard apparently enjoys being a creative SQL Server professional. Contact Richard Ding at rding@rcn.com. Copyright 2008 TechTarget