Home > General > Finding number of rows and disk space in SQL Server

Finding number of rows and disk space in SQL Server

To see how many rows a table contains or how much disk space a table is using, we can use the system stored procedure sp_spacedused to quickly return information about a table or the database.

For a single table, we can pass the table name to the stored procedure:

We can execute the stored procedure without passing a table name to see how much disk space the entire database is using:

We can combine this system stored procedure with the undocumented stored procedure sp_msforeachtable to view the number of rows and disk space usage for all tables in the current database:

However, we have to keep in mind that the numbers being reported by sp_spaceused may not be immediately accurate after an index or table has been dropped, a table has been truncated, etc… since the information is not maintained in real time. The command dbcc updateusage may be execute to update the information for a single table or the entire database. Similarly, the sp_spaceused stored procedure may be executed with an additional parameter to update the information for a single table or the entire database:

-- Single table
sp_spaceused @objname = 'Person.Address', @updateusage = 'true'

-- Entire database
sp_spaceused @updateusage = ‘true’

Although we’ve executed the update commands above, the row counts may still be inaccurate. The commands above only updates the disk space usage information, but not the row count information stored in the table sysindexes. We may execute the command dbcc updateusage with the count_rows argument to update sysindexes:

-- Single table
dbcc updateusage ('AdventureWorks', 'Person.Address') with count_rows

-- Entire database
dbcc updateusage (0) with count_rows
  1. September 24, 2014 at 11:19 am

    Thanks for sharing your thoughts on kana fairy tail. Regards

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: