Generate sp_spaceused Commands for All User Tables in T-SQL

Checking how much space every table in a database uses means running sp_spaceused once per table, and typing those calls by hand stops being practical after a dozen tables. This one-line query reads the object catalog and writes the sp_spaceused call for every user table, so the whole set can be pasted into a query window and run in one pass. Each generated call returns the table's row count and its reserved, data, index and unused space.

Purpose and Overview

Capacity questions usually start small: which tables are growing, which ones are carrying more index than data, and where reserved space is sitting unused. sp_spaceused answers all three for a single object. Given a table name it returns the number of rows, the disk space reserved for the table, the space used by data, the space used by indexes, and the space reserved but not yet used. Called with no name, it reports on the whole database instead.

The limitation is that it takes one object at a time. A database with two hundred tables needs two hundred calls, and nobody wants to type them. The script below solves that by turning the catalog into a list of ready-to-run commands. It filters the object catalog to user tables only, so views, procedures, constraints and system objects are left out, and it sorts the output by name so the results come back in a predictable order.

The output is plain text: one EXEC sp_spaceused line per table. Paste it into SQL Server Management Studio or a sqlcmd session and run it, and each line returns its own result set. For a quick look at a handful of tables that is all you need. For a whole-database size report, the single-query alternative in the Practical Tips section returns everything as one result set that can be sorted and saved.

Code Breakdown

The complete script is a single SELECT against the sysobjects compatibility view:

1select 'EXEC sp_spaceused ' + name from sysobjects where type = 'U' order by name

The generated statement

The query concatenates the literal string 'EXEC sp_spaceused ' with each object's name, so every row of output is a complete statement such as EXEC sp_spaceused Customers. The EXEC keyword matters once the lines are run together. Microsoft's documentation for the GO command states that any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword, so without it only the first line of the pasted output would run as written.

The query does not append GO. GO is not a Transact-SQL statement; it is a batch separator recognized by the sqlcmd and osql utilities and the SQL Server Management Studio Code Editor, and a Transact-SQL statement cannot occupy the same line as a GO command. Putting GO on the end of each generated line would produce lines the editor cannot run, so batching is left to you.

Filtering to user tables

sysobjects holds one row for each object created within a database, including constraints, defaults, rules and stored procedures. The type column identifies what each row is, and type = 'U' means a user table. That single predicate is what keeps views, procedures, triggers and system tables out of the list.

Ordering the output

order by name sorts the generated commands alphabetically by table name. When the output is run, the result sets come back in the same order, which makes it easy to find a specific table in a long run of results and to compare two runs taken on different days.

Sample output

Run against a small sales database, the query returns rows like these:

1EXEC sp_spaceused Customers
2EXEC sp_spaceused Orders
3EXEC sp_spaceused Products
4EXEC sp_spaceused Suppliers

A schema-safe variant

The original query uses the bare table name, which breaks in two common cases: tables outside the caller's default schema, and table names that contain spaces or reserved words. The variant below builds a schema-qualified, delimited name with QUOTENAME, which returns a Unicode string with the delimiters added to make the input a valid SQL Server delimited identifier:

1select 'EXEC sp_spaceused N''' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ''''
2from sys.tables
3order by SCHEMA_NAME(schema_id), name

sys.tables returns a row for each user table in the database and inherits its columns from sys.objects, including schema_id, the ID of the schema that contains the object. sp_spaceused accepts a qualified or nonqualified name in its @objname parameter, so the schema-qualified form works directly.

Key Benefits and Use Cases

• No hand-typed calls — one query writes every sp_spaceused statement, however many tables the database holds.
• User tables only — the type = 'U' filter leaves out views, procedures, constraints and system objects.
• Familiar output — each call returns the standard sp_spaceused columns: rows, reserved, data, index_size and unused.
• Index-heavy tables stand out — comparing index_size with data for each table shows where indexes take more space than the data they cover.
• Reserved but unused space — the unused column shows space allocated to a table that it is not yet using.
• Repeatable — the alphabetical order makes two runs on different dates easy to compare line by line.

Performance Considerations

• The generator is metadata-only: the SELECT reads the object catalog, not table data, so it returns quickly even on large databases.
• @updateusage is expensive: sp_spaceused can run DBCC UPDATEUSAGE first when @updateusage is set to true, but Microsoft notes it can take some time on large tables or databases and should be used only when you suspect incorrect values.
• Deferred drops skew the numbers: after large indexes are dropped or rebuilt, or large tables are dropped or truncated, the Database Engine defers the page deallocations until the transaction commits, so sp_spaceused run immediately afterwards might not reflect the space actually available.
• sysobjects is deprecated: Microsoft describes it as a SQL Server 2000 system table kept as a view for backward compatibility, recommends the current system views instead, and says it will be removed in a future version. The sys.tables variant above is the forward-compatible choice.
• One result set per table: running hundreds of generated calls returns hundreds of separate result sets, which is awkward to save or sort. The DMV query in the next section avoids that.

Practical Tips

• Mind metadata visibility — the visibility of metadata is limited to securables that a user owns or has been granted some permission on, so a low-privileged login sees only some tables in the generated list. Run it with an account that can see every table you need.
• Know who can run what — permission to execute sp_spaceused is granted to the public role, but only members of the db_owner fixed database role can specify @updateusage.
• Use one query for a whole-database report — sys.dm_db_partition_stats returns page and row-count information for every partition in the current database, and the total for a table is the sum across its partitions:

1select QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) as table_name,
2       sum(case when ps.index_id in (0, 1) then ps.row_count else 0 end) as row_count,
3       sum(ps.reserved_page_count) as reserved_pages,
4       sum(ps.used_page_count) as used_pages
5from sys.dm_db_partition_stats ps
6join sys.objects o on o.object_id = ps.object_id
7where o.type = 'U'
8group by o.schema_id, o.name
9order by reserved_pages desc

• Check the DMV's permissions first — querying sys.dm_db_partition_stats requires VIEW DATABASE STATE and VIEW DEFINITION; on SQL Server 2022 and later it requires VIEW DATABASE PERFORMANCE STATE and VIEW SECURITY DEFINITION on the database.
• Treat row counts as approximate — Microsoft documents the DMV's row_count column as the approximate number of rows in the partition; use COUNT(*) when an exact figure matters.
• Keep a history — insert the DMV query's output into a dated table from a SQL Server Agent job, and growth trends fall out of a simple comparison between two dates.

Conclusion

Generating sp_spaceused calls from the catalog turns a tedious, table-by-table job into one query and a paste. It is the quick first look at where space is going in a database; for regular whole-database reporting, the schema-safe sys.tables variant and the sys.dm_db_partition_stats query are the versions to keep in the toolkit.

References

• sp_spaceused (Transact-SQL) — Parameters, result columns, the @updateusage and deferred-drop notes, and permissions.
• sys.sysobjects (Transact-SQL) — The compatibility view the script reads, its type codes, and its deprecation notice.
• sys.tables (Transact-SQL) — The current catalog view with one row per user table.
• sys.objects (Transact-SQL) — The base catalog view and its schema_id and type columns.
• SQL Server Utilities Statements - GO — Why GO is a client batch separator and when EXECUTE is required.
• QUOTENAME (Transact-SQL) — Building delimited identifiers for schema and table names.
• sys.dm_db_partition_stats (Transact-SQL) — Page and row counts per partition, and the permissions the DMV requires.
• Metadata visibility configuration — Why users see only the objects they own or have permission on.