How to find the size of a table in SQL

Question

User · Answer

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you.

Syntax:

 sp_spaceused 'Tablename'

see in :

http://www.howtogeek.com/howto/database/determine-size-of-a-table-in-sql-server/

User · Answer

You may refer the answer by Marc s in another thread  Very useful   Get size of all tables in database

User · Answer

A query  modification of https   stackoverflow com a 7892349 1737819  to find a custom name table size in GB  You might try this  replace  YourTableName  with the name of your table   SELECT      t NAME AS TableName          p rows AS RowCounts      CONVERT DECIMAL SUM a total pages     8   1024   1024 AS TotalSpaceGB       SUM a used pages     8   1024   1024 AS UsedSpaceGB         SUM a total pages  - SUM a used pages     8   1024   1024 AS UnusedSpaceGB FROM      sys tables t INNER JOIN           sys indexes i ON t OBJECT ID   i object id INNER JOIN      sys partitions p ON i object id   p OBJECT ID AND i index id   p index id INNER JOIN      sys allocation units a ON p partition id   a container id LEFT OUTER JOIN      sys schemas s ON t schema id   s schema id WHERE      t NAME    YourTable      AND t is ms shipped   0     AND i OBJECT ID  gt  255  GROUP BY      t Name  s Name  p Rows ORDER BY      UsedSpaceGB DESC  t Name

User · Answer

SQL Server  nicely formatted table for all tables in KB MB   SELECT      t NAME AS TableName      s Name AS SchemaName      p rows AS RowCounts      SUM a total pages    8 AS TotalSpaceKB       CAST ROUND   SUM a total pages    8    1024 00   2  AS NUMERIC 36  2   AS TotalSpaceMB      SUM a used pages    8 AS UsedSpaceKB       CAST ROUND   SUM a used pages    8    1024 00   2  AS NUMERIC 36  2   AS UsedSpaceMB        SUM a total pages  - SUM a used pages     8 AS UnusedSpaceKB      CAST ROUND   SUM a total pages  - SUM a used pages     8    1024 00  2  AS NUMERIC 36  2   AS UnusedSpaceMB FROM      sys tables t INNER JOIN           sys indexes i ON t OBJECT ID   i object id INNER JOIN      sys partitions p ON i object id   p OBJECT ID AND i index id   p index id INNER JOIN      sys allocation units a ON p partition id   a container id LEFT OUTER JOIN      sys schemas s ON t schema id   s schema id WHERE      t NAME NOT LIKE  dt        AND t is ms shipped   0     AND i OBJECT ID  gt  255  GROUP BY      t Name  s Name  p Rows ORDER BY      t Name

User · Answer

Do you by size mean the number of records in the table  by any chance  In that case   SELECT COUNT    FROM your table name

User · Answer

And in PostgreSQL   SELECT pg size pretty pg relation size  tablename

User · Answer

SQL Server -  sp spaceused  TableName    Or in management studio  Right Click on table -  Properties -  Storage  MySQL -  SELECT table schema  table name  data length  index length FROM information schema tables   Sybase -  sp spaceused  TableName    Oracle - how-do-i-calculate-tables-size-in-oracle

User · Answer

SQL Server provides a built-in stored procedure that you can run to easily show the size of a table  including the size of the indexes  sp spaceused    Tablename

User · Answer

I know that in SQL 2012  may work in other versions  you can do the following    Right click on the database name in the Object Explorer  Select Reports   Standard Reports   Disk Usage by Top Tables    That will give you a list of the top 1000 tables and then you can order it by data size etc

User · Answer

Combining the answers from ratty s and Haim s posts  including comments  I ve come up with this  which for SQL Server seems to be the most elegant so far   -- DROP TABLE  tmpTableSizes CREATE TABLE  tmpTableSizes       tableName varchar 100       numberofRows varchar 100       reservedSize varchar 50       dataSize varchar 50       indexSize varchar 50       unusedSize varchar 50    insert  tmpTableSizes EXEC sp MSforeachtable  command1  EXEC sp spaceused        select    from  tmpTableSizes order by cast LEFT reservedSize  LEN reservedSize  - 4  as int   desc   This gives you a list of all your tables in order of reserved size  ordered from largest to smallest

User · Answer

Here s a simple query  if you are just trying to find the largest tables     -- Find largest table partitions  SELECT top 20 obj name  LTRIM  STR   sz in row data page count   8  15  0      KB   as Size    FROM sys dm db partition stats sz inner join sys objects obj on obj object id   sz object id   order by sz in row data page count desc

[sql] How to find the size of a table in SQL?

Examples related to sql