Get size of all tables in database

Question

I have inherited a fairly large SQL Server database  It seems to take up more space than I would expect  given the data it contains   Is there an easy way to determine how much space on disk each table is consuming

User · Answer

We use table partitioning and had some trouble with the queries provided above due to duplicate records.

For those who need this, you can find below the query as run by SQL Server 2014 when generating the "Disk usage by table" report. I assume it also works with previous versions of SQL Server.

It works like a charm.

SELECT
    a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, 
    a1.data * 8 AS data,
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
    (SELECT 
        ps.object_id,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN row_count
                ELSE 0
            END
            ) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (
            CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
        WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name

User · Answer

From a command prompt using OSQL   OSQL -E -d  lt  databasename  gt  -Q  exec sp msforeachtable  sp spaceused        gt  result txt

User · Answer

Here is a sample query to get tables larger than 1GB ordered by size descending   USE YourDB GO  DECLARE  Mult float   8 SET  Mult    Mult   POWER 2  20  -- Use POWER 2  10  for MBs    WITH CTE AS   SELECT     i object id      Rows   MAX p rows       TotalSpaceGB   ROUND SUM a total pages     Mult  0       UsedSpaceGB   ROUND SUM a used pages     Mult  0  FROM      sys indexes i JOIN     sys partitions p ON i object id   p object id AND i index id   p index id JOIN     sys allocation units a ON p partition id   a container id WHERE     i object id  gt  255 GROUP BY     i object id HAVING     SUM a total pages     Mult  gt  1   SELECT      SchemaName   s name      TableName   t name      c TotalSpaceGB      c UsedSpaceGB      UnusedSpaceGB   c TotalSpaceGB - c UsedSpaceGB       RowCount    c Rows FROM      CTE c JOIN         sys tables t ON t object id   c object id JOIN     sys schemas s ON t schema id   s schema id ORDER BY     c TotalSpaceGB DESC

User · Answer

After some searching  I could not find an easy way to get information on all of the tables  There is a handy stored procedure named sp spaceused that will return all of the space used by the database  If provided with a table name  it returns the space used by that table  However  the results returned by the stored procedure are not sortable  since the columns are character values   The following script will generate the information I m looking for   create table  TableSize       Name varchar 255        rows  int      reserved varchar 255       data varchar 255       index size varchar 255       unused varchar 255   create table  ConvertedSizes       Name varchar 255        rows  int      reservedKb int      dataKb int      reservedIndexSize int      reservedUnused int   EXEC sp MSforeachtable  command1  insert into  TableSize EXEC sp spaceused      insert into  ConvertedSizes  Name   rows   reservedKb  dataKb  reservedIndexSize  reservedUnused  select name   rows    SUBSTRING reserved  0  LEN reserved -2    SUBSTRING data  0  LEN data -2    SUBSTRING index size  0  LEN index size -2    SUBSTRING unused  0  LEN unused -2  from  TableSize  select   from  ConvertedSizes order by reservedKb desc  drop table  TableSize drop table  ConvertedSizes

User · Answer

Extension to  xav answer that handled table partitions to get size in MB and GB  Tested on SQL Server 2008 2012  Commented a line where is memory optimized   1   SELECT     a2 name AS TableName      a1 rows as  RowCount       -- a1 reserved   ISNULL a4 reserved 0     8 AS ReservedSize KB      --a1 data   8 AS DataSize KB      -- CASE WHEN  a1 used   ISNULL a4 used 0    gt  a1 data THEN  a1 used   ISNULL a4 used 0   - a1 data ELSE 0 END    8 AS IndexSize KB      -- CASE WHEN  a1 reserved   ISNULL a4 reserved 0    gt  a1 used THEN  a1 reserved   ISNULL a4 reserved 0   - a1 used ELSE 0 END    8 AS UnusedSize KB      CAST ROUND   a1 reserved   ISNULL a4 reserved 0     8    1024 00  2  AS NUMERIC 36  2   AS ReservedSize MB      CAST ROUND a1 data   8   1024 00  2  AS NUMERIC 36  2   AS DataSize MB      CAST ROUND  CASE WHEN  a1 used   ISNULL a4 used 0    gt  a1 data THEN  a1 used   ISNULL a4 used 0   - a1 data ELSE 0 END    8   1024 00  2  AS NUMERIC 36  2   AS IndexSize MB      CAST ROUND  CASE WHEN  a1 reserved   ISNULL a4 reserved 0    gt  a1 used THEN  a1 reserved   ISNULL a4 reserved 0   - a1 used ELSE 0 END    8   1024 00  2  AS NUMERIC 36  2   AS UnusedSize MB      --      Separator MB GB      CAST ROUND   a1 reserved   ISNULL a4 reserved 0     8    1024 00   1024 00  2  AS NUMERIC 36  2   AS ReservedSize GB      CAST ROUND a1 data   8   1024 00   1024 00  2  AS NUMERIC 36  2   AS DataSize GB      CAST ROUND  CASE WHEN  a1 used   ISNULL a4 used 0    gt  a1 data THEN  a1 used   ISNULL a4 used 0   - a1 data ELSE 0 END    8   1024 00   1024 00  2  AS NUMERIC 36  2   AS IndexSize GB      CAST ROUND  CASE WHEN  a1 reserved   ISNULL a4 reserved 0    gt  a1 used THEN  a1 reserved   ISNULL a4 reserved 0   - a1 used ELSE 0 END    8   1024 00   1024 00  2  AS NUMERIC 36  2   AS UnusedSize GB FROM      SELECT          ps object id          SUM  CASE WHEN  ps index id  lt  2  THEN row count ELSE 0 END  AS  rows           SUM  ps reserved page count  AS reserved          SUM  CASE                 WHEN  ps index id  lt  2  THEN  ps in row data page count   ps lob used page count   ps row overflow used page count                  ELSE  ps lob used page count   ps row overflow used page count              END               AS data          SUM  ps used page count  AS used     FROM sys dm db partition stats ps         --   Remove the following comment for SQL Server 2014          --WHERE ps object id NOT IN  SELECT object id FROM sys tables WHERE is memory optimized   1      GROUP BY ps object id  AS a1 LEFT OUTER JOIN       SELECT          it parent id          SUM ps reserved page count  AS reserved          SUM ps used page count  AS used      FROM sys dm db partition stats ps      INNER JOIN sys internal tables it ON  it object id   ps object id       WHERE it internal type IN  202 204       GROUP BY it parent id  AS a4 ON  a4 parent id   a1 object id  INNER JOIN sys all objects a2  ON   a1 object id   a2 object id    INNER JOIN sys schemas a3 ON  a2 schema id   a3 schema id  WHERE a2 type  lt  gt  N S  and a2 type  lt  gt  N IT  --AND a2 name    MyTable        --Filter for specific table --ORDER BY a3 name  a2 name ORDER BY ReservedSize MB DESC

User · Answer

I added a few more columns on top of marc s answer   with fs as   select i object id          p rows AS RowCounts          SUM a total pages    8 AS TotalSpaceKb from     sys indexes i 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 WHERE      i OBJECT ID  gt  255  GROUP BY      i object id      p rows    SELECT      t NAME AS TableName      fs RowCounts      fs TotalSpaceKb      t create date      t modify date        select COUNT 1          from sys columns c          where c object id   t object id   TotalColumns     FROM      sys tables t INNER JOIN           fs  ON t OBJECT ID   fs object id WHERE      t NAME NOT LIKE  dt        AND t is ms shipped   0 ORDER BY      t Name

User · Answer

For Azure I used this  You should have SSMS v17 x  I used   With this  as User  Sparrow has mentioned  Open your Databases gt  and select Tables  Then press key F7 You should see the row count as   SSMS here is connected to Azure databases

User · Answer

The currently accepted answer has over 2600 up-votes but gives incorrect results when working with multiple partitions and or filtered indexes  It also doesn t distinguish between the size of the data and indexes  which is often very relevant  Several suggested fixes don t address the core problem or are simply wrong as well  The following query addresses all of those issues  SELECT        object id           t  object id        schema name         s  name        table name          t  name        index name          CASE WHEN i  type  in  0 1 5  THEN null    ELSE i  name  END -- 0 Heap  1 Clustered  5 Clustered Columnstore       object type         CASE WHEN i  type  in  0 1 5  THEN  TABLE  ELSE  INDEX   END       index type          i  type desc        partition count     p partition count       row count           p  rows        data compression    CASE WHEN p data compression cnt  gt  1 THEN  Mixed                                 ELSE    SELECT DISTINCT p data compression desc                                        FROM sys partitions p                                        WHERE i  object id    p  object id  AND i index id   p index id                                                                 END       total space MB      cast round   au total pages                     8 1024 00    2  AS DECIMAL 36 2         used space MB       cast round   au used pages                      8 1024 00    2  AS DECIMAL 36 2         unused space MB     cast round   au total pages - au used pages     8 1024 00    2  AS DECIMAL 36 2   FROM sys schemas s JOIN sys tables  t ON s schema id   t schema id JOIN sys indexes i ON t object id   i object id JOIN       SELECT  object id   index id  partition count count      rows  sum  rows    data compression cnt count distinct  data compression       FROM sys partitions     GROUP BY  object id    index id    p ON i  object id    p  object id  AND i  index id    p  index id  JOIN       SELECT p  object id   p  index id   total pages   sum a total pages   used pages   sum a used pages   data pages sum a data pages      FROM sys partitions p     JOIN sys allocation units a ON p  partition id    a  container id      GROUP BY p  object id   p  index id    au ON i  object id    au  object id  AND i  index id    au  index id  WHERE t is ms shipped   0 -- Not a system table

User · Answer

If you need to calculate exactly the same numbers  that are on  table properties - storage  page in SSMS  you need to count them with the same method as it done in SSMS  works for sql server 2005 and above     and also works correctly for tables with LOB fields - because just counting  used pages  is not enought to show accurate index size     with cte as   SELECT t name as TableName  SUM  s used page count  as used pages count  SUM  CASE             WHEN  i index id  lt  2  THEN  in row data page count   lob used page count   row overflow used page count              ELSE lob used page count   row overflow used page count         END  as pages FROM sys dm db partition stats  AS s  JOIN sys tables AS t ON s object id   t object id JOIN sys indexes AS i ON i  object id    t  object id  AND s index id   i index id GROUP BY t name   select     cte TableName       cast  cte pages   8   1024 as decimal 10 3   as TableSizeInMB       cast   CASE WHEN cte used pages count  gt  cte pages                  THEN cte used pages count - cte pages                 ELSE 0            END    8  1024  as decimal 10 3   as IndexSizeInMB from cte order by 2 desc

User · Answer

I found this query to be easy to use  amp  quick  select schema name tab schema id          tab name as  table    cast sum spc used pages   8  1024 00 as numeric 36  2   as used mb  cast sum spc total pages   8  1024 00 as numeric 36  2   as allocated mb from sys tables  nolock  tab inner join sys indexes  nolock  ind      on tab object id   ind object id inner join sys partitions   nolock  part      on ind object id   part object id and ind index id   part index id inner join sys allocation units  nolock  spc     on part partition id   spc container id group by schema name tab schema id          tab name order by sum spc used pages  desc

User · Answer

-- Show the size of all the tables in a database sort by data size descending SET NOCOUNT ON DECLARE  TableInfo TABLE  tablename varchar 255   rowcounts int  reserved varchar 255   DATA varchar 255   index size varchar 255   unused varchar 255   DECLARE  cmd1 varchar 500  SET  cmd1    exec sp spaceused         INSERT INTO  TableInfo  tablename rowcounts reserved DATA index size unused  EXEC sp msforeachtable  command1  cmd1  SELECT   FROM  TableInfo ORDER BY Convert int Replace DATA   KB       DESC

User · Answer

Above queries are good for finding the amount of space used by the table  indexes included   but if you want to compare how much space is used by indexes on the table use this query   SELECT     OBJECT NAME i OBJECT ID  AS TableName      i name AS IndexName      i index id AS IndexID      8   SUM a used pages  AS  Indexsize KB   FROM     sys indexes AS i     JOIN sys partitions AS p ON p OBJECT ID   i OBJECT ID AND p index id   i index id     JOIN sys allocation units AS a ON a container id   p partition id WHERE     i is primary key   0 -- fix for size discrepancy GROUP BY     i OBJECT ID      i index id      i name ORDER BY     OBJECT NAME i OBJECT ID       i index id

User · Answer

My post is only relevant for SQL Server 2000 and has been tested to work in my environment   This code accesses All possible databases of a single instance  not just a single database     I use two temp tables to help collect the appropriate data and then dump the results into one  Live  table   Returned data is  DatabaseName  DatabaseTableName  Rows  in the Table   data  size of the table in KB it would seem   entry data  I find this useful for knowing when I last ran the script      Downfall to this code is the  data  field is not stored as an int  The chars  KB  are kept in that field   and that would be useful  but not totally necessary  for sorting   Hopefully this code helps someone out there and saves them some time   CREATE PROCEDURE  dbo   usp getAllDBTableSizes   AS BEGIN    SET NOCOUNT OFF     CREATE TABLE  DatabaseTables  dbname  sysname TableName sysname     CREATE TABLE  AllDatabaseTableSizes Name sysname  rows  VARCHAR 18   reserved VARCHAR 18   data VARCHAR 18   index size VARCHAR 18   unused VARCHAR 18       DECLARE  SQL nvarchar 4000     SET  SQL  select       AS  Database   Table Name from     information schema tables WHERE TABLE TYPE     BASE TABLE         INSERT INTO  DatabaseTables DbName  TableName        EXECUTE sp msforeachdb  Command1  SQL     DECLARE AllDatabaseTables CURSOR LOCAL READ ONLY FOR       SELECT TableName FROM  DatabaseTables     DECLARE AllDatabaseNames CURSOR LOCAL READ ONLY FOR       SELECT DBName FROM  DatabaseTables     DECLARE  DBName sysname      OPEN AllDatabaseNames       DECLARE  TName sysname    OPEN AllDatabaseTables       WHILE 1 1 BEGIN        FETCH NEXT FROM AllDatabaseNames INTO  DBName         FETCH NEXT FROM AllDatabaseTables INTO  TName        IF   FETCH STATUS lt  gt 0 BREAK         INSERT INTO  AllDatabaseTableSizes          EXEC    EXEC      DBName     dbo sp spaceused      TName       END      --http   msdn microsoft com en-us library aa175920 v sql 80  aspx    INSERT INTO rsp DatabaseTableSizes  DatabaseName  name   rows   data        SELECT    dbname   name   rows    data FROM  DatabaseTables       INNER JOIN  AllDatabaseTableSizes       ON  DatabaseTables TableName    AllDatabaseTableSizes Name       GROUP BY  dbname    name   rows    data       ORDER BY  dbname     --To be honest  I have no idea what exact duplicates we are dropping     -- but in my case a near enough approach has been good enough     DELETE FROM  rsp DatabaseTableSizes     WHERE name IN                 SELECT name        FROM  rsp DatabaseTableSizes        GROUP BY name       HAVING COUNT     gt  1             DROP TABLE  DatabaseTables    DROP TABLE  AllDatabaseTableSizes     CLOSE AllDatabaseTables      DEALLOCATE AllDatabaseTables       CLOSE AllDatabaseNames      DEALLOCATE AllDatabaseNames       END  --EXEC  dbo   usp getAllDBTableSizes     In case you need to know  the rsp DatabaseTableSizes table was created through   CREATE TABLE  dbo   rsp DatabaseSizes        DatabaseName   varchar  1000  NULL       dbSize   decimal  15  2  NULL       DateUpdated   smalldatetime  NULL   ON  PRIMARY   GO

User · Answer

If you are using SQL Server Management Studio  SSMS   instead of running a query  which in my case returned duplicate rows  you can run a standard report  nbsp    Right click on the database Navigate to Reports   Standard Reports   Disk Usage By Table   Note  The database compatibility level must be set to 90 or above for this to work correctly  See http   msdn microsoft com en-gb library bb510680 aspx

User · Answer

IF you only care about the empty wasted space in the database and not the individual tables you can consider the following  If the database experiences large amount of data insertion and then deletions  maybe like in an ETL situation  this will cause too much unused space in the database as the file groups auto-grows but never auto-shrinks back  You can see if this is the case by using the Properties page of your database  You can shrink  right click on the database  gt  Tasks  gt  Shrink  and claim some space back  But  if the underlying reason is still there  the database will grow back  and consume extra time trying to grow back and things gets slowed down until it grows enough - so don t do it in that case

User · Answer

Riffing on  Mark answer above  added the  updateusage  true  to force the latest size stats  https   msdn microsoft com en-us library ms188776 aspx            SET NOCOUNT ON         DECLARE  TableInfo TABLE  tablename varchar 255   rowcounts int  reserved varchar 255   DATA varchar 255   index size varchar 255   unused varchar 255           DECLARE  cmd1 varchar 500          SET  cmd1    exec sp spaceused  objname          updateusage    true              INSERT INTO  TableInfo  tablename rowcounts reserved DATA index size unused          EXEC sp msforeachtable  command1  cmd1  SELECT   FROM  TableInfo ORDER BY Convert int Replace DATA   KB       DESC

User · Answer

exec  sp spaceused N dbo MyTable    For all tables  use   adding from the comments of Paul   exec sp MSForEachTable  exec sp spaceused

User · Answer

A small change on Mar c s answer  since I have been going back to this page so often  ordered by most row s first   SELECT     t NAME AS TableName      s Name AS SchemaName      p rows AS RowCounts      SUM a total pages    8 AS TotalSpaceKB      SUM a used pages    8 AS UsedSpaceKB       SUM a total pages  - SUM a used pages     8 AS UnusedSpaceKB 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     --p rows DESC --Uncomment to order by amount rows instead of size in KB      SUM a total pages  DESC

User · Answer

This will give you the sizes  and record counts for each table   set ANSI NULLS ON set QUOTED IDENTIFIER ON GO -- Get a list of tables and their sizes on disk ALTER PROCEDURE  dbo   sp Table Sizes  AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements      SET NOCOUNT ON  DECLARE  table name VARCHAR 500    DECLARE  schema name VARCHAR 500    DECLARE  tab1 TABLE           tablename VARCHAR  500  collate database default          schemaname VARCHAR 500  collate database default      CREATE TABLE  temp Table            tablename sysname          row count INT          reserved VARCHAR 50  collate database default          data VARCHAR 50  collate database default          index size VARCHAR 50  collate database default          unused VARCHAR 50  collate database default       INSERT INTO  tab1   SELECT Table Name  Table Schema   FROM information schema tables   WHERE TABLE TYPE    BASE TABLE    DECLARE c1 CURSOR FOR  SELECT Table Schema         Table Name    FROM information schema tables t1   WHERE TABLE TYPE    BASE TABLE    OPEN c1  FETCH NEXT FROM c1 INTO  table name  WHILE   FETCH STATUS   0   BEGIN            SET  table name   REPLACE  table name                     SET  table name   REPLACE  table name                      -- make sure the object exists before calling sp spacedused          IF EXISTS SELECT id FROM sysobjects WHERE id   OBJECT ID  table name            BEGIN                 INSERT INTO  temp Table EXEC sp spaceused  table name  false           END           FETCH NEXT FROM c1 INTO  table name  END  CLOSE c1  DEALLOCATE c1   SELECT  t1            t2 schemaname   FROM  temp Table t1   INNER JOIN  tab1 t2 ON  t1 tablename   t2 tablename    ORDER BY schemaname t1 tablename    DROP TABLE  temp Table END

User · Answer

SELECT      t NAME AS TableName      s Name AS SchemaName      p rows      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      TotalSpaceMB DESC  t Name

User · Answer

sp spaceused can get you information on the disk space used by a table  indexed view  or the whole database   For example   USE MyDatabase  GO  EXEC sp spaceused N User ContactInfo   GO   This reports the disk usage information for the ContactInfo table   To use this for all tables at once   USE MyDatabase  GO  sp msforeachtable  EXEC sp spaceused      GO   You can also get disk usage from within the right-click Standard Reports functionality of SQL Server   To get to this report  navigate from the server object in Object Explorer  move down to the Databases object  and then right-click any database  From the menu that appears  select Reports  then Standard Reports  and then  Disk Usage by Partition   DatabaseName

User · Answer

Here is another method  using SQL Server Management Studio  in Object Explorer  go to your database and select Tables    Then open the Object Explorer Details  either by pressing F7 or going to View- Object Explorer Details   In the object explorer details page  right click on the column header and enable the columns that you would like to see in the page  You can sort the data by any column too

User · Answer

As a simple extension to marc s s answer  the one that has been accepted   this is adjusted to return column count and allow for filtering   SELECT   FROM    SELECT      t NAME AS TableName      s Name AS SchemaName      p rows AS RowCounts      COUNT DISTINCT c COLUMN NAME  as ColumnCount      SUM a total pages    8 AS TotalSpaceKB        SUM a used pages    8  AS UsedSpaceKB        SUM a total pages  - SUM a used pages     8 AS UnusedSpaceKB 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 INNER JOIN     INFORMATION SCHEMA COLUMNS c ON t NAME   c TABLE NAME 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   AS Result  WHERE     RowCounts  gt  1000     AND ColumnCount  gt  10 ORDER BY      UsedSpaceKB DESC

User · Answer

For get all table size in one database you can use this query    Exec sys sp MSforeachtable   sp spaceused         And you can change it to insert all of result into temp table and after that select from temp table   Insert into  TempTable Exec sys sp MSforeachtable   sp spaceused        Select   from  TempTable

User · Answer

Here is a way to get all tables  sizes quickly with the following steps    Write the given T-SQL commands to list all database tables   select  exec sp spaceused     TABLE NAME from INFORMATION SCHEMA TABLES where TABLE TYPE    BASE TABLE   Now copy the list of database tables  and copy it into a new query analyzer window  exec sp spaceused table1 exec sp spaceused table2 exec sp spaceused table3 exec sp spaceused table4 exec sp spaceused table5  In SQL query analyzer  select from top tool bar option Results to file  Ctrl   Shift   F   Now finally hit the Execute button red marked from the above tool bar  The Database size of all tables is now stored in a file on your computer

[sql-server] Get size of all tables in database

Examples related to sql-server

Examples related to tsql