Determine Size of a Table in SQL Server

by ebarcza 7/31/2012 9:46:00 AM

sp_spaceused ‘Tablename’

 

 

   1:  CREATE PROCEDURE GetAllTableSizes
   2:  AS
   3:  /*
   4:      Obtains spaced used data for ALL user tables in the database
   5:  */
   6:  DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
   7:   
   8:  --Cursor to get the name of all user tables from the sysobjects listing
   9:  DECLARE tableCursor CURSOR
  10:  FOR 
  11:  select [name]
  12:  from dbo.sysobjects 
  13:  where  OBJECTPROPERTY(id, N'IsUserTable') = 1
  14:  FOR READ ONLY
  15:   
  16:  --A procedure level temp table to store the results
  17:  CREATE TABLE #TempTable
  18:  (
  19:      tableName varchar(100),
  20:      numberofRows varchar(100),
  21:      reservedSize varchar(50),
  22:      dataSize varchar(50),
  23:      indexSize varchar(50),
  24:      unusedSize varchar(50)
  25:  )
  26:   
  27:  --Open the cursor
  28:  OPEN tableCursor
  29:   
  30:  --Get the first table name from the cursor
  31:  FETCH NEXT FROM tableCursor INTO @TableName
  32:   
  33:  --Loop until the cursor was not able to fetch
  34:  WHILE (@@Fetch_Status >= 0)
  35:  BEGIN
  36:      --Dump the results of the sp_spaceused query to the temp table
  37:      INSERT  #TempTable
  38:          EXEC sp_spaceused @TableName
  39:   
  40:      --Get the next table name
  41:      FETCH NEXT FROM tableCursor INTO @TableName
  42:  END
  43:   
  44:  --Get rid of the cursor
  45:  CLOSE tableCursor
  46:  DEALLOCATE tableCursor
  47:   
  48:  --Select all records so we can use the reults
  49:  SELECT * 
  50:  FROM #TempTable
  51:   
  52:  --Final cleanup!
  53:  DROP TABLE #TempTable
  54:   
  55:  GO 

Tags:

SQL SERVER

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  July 2018  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2018

Sign in