Space allocation in all the tables in a database

The below query batch will display the space used by all the tables in a database:

SET NOCOUNT ON

USE <DATABASE Name>

–The below DBCC command will correct the pages and row count inaccuracies in catalog views.
DBCC UPDATEUSAGE(0)

— The below command will display Database size.
EXEC sp_spaceused

— The below portion will display the space used by all the tables in the database
CREATE TABLE #temptable
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #temptable EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

SELECT *
FROM   #temptable

— # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #temptable
 
DROP TABLE #temptable

Advertisements

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: