Skip to content
Fix Code Error

Get size of all tables in database

March 13, 2021 by Code Error
Posted By: Anonymous

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?

Solution

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 > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name
Answered By: Anonymous

Related Articles

  • SQL query return data from multiple tables
  • data.table vs dplyr: can one do something well the…
  • Get Table and Index storage size in sql server
  • AppCompat v7 r21 returning error in values.xml?
  • How to find largest objects in a SQL Server database?
  • How can I pass a wct test while rearranging children…
  • When should I use a table variable vs temporary…
  • How does database indexing work?
  • Select SQL Server database size
  • Query to list number of records in each table in a database
  • Sort table rows In Bootstrap
  • Start redis-server with config file
  • Log record changes in SQL server in an audit table
  • How do you clear the SQL Server transaction log?
  • "Large data" workflows using pandas
  • How to allocate aligned memory only using the…
  • Search all tables, all columns for a specific value…
  • Database development mistakes made by application developers
  • how to show progress bar(circle) in an activity…
  • Text size and different android screen sizes
  • Search for one value in any column of any table…
  • T-SQL How to create tables dynamically in stored procedures?
  • How can I send emails through SSL SMTP with the .NET…
  • Active tab issue on page load HTML
  • What's the difference between a temp table and table…
  • Use SQL Server Management Studio to connect remotely…
  • Memcached vs. Redis?
  • Find a value anywhere in a database
  • Find a string by searching all tables in SQL Server…
  • Using Auto Layout in UITableView for dynamic cell…
  • ASP.Net Core Razor Page - Pass Selected Name From…
  • How to create a temporary table in SSIS control flow…
  • How do I obtain a Query Execution Plan in SQL Server?
  • LNK2019 símbolo externo public: bool __thiscall ……
  • Webpack build error
  • Filtering DataGridView without changing datasource
  • How to shrink temp tablespace in oracle?
  • How to check if a table exists in a given schema
  • How can I get column names from a table in SQL Server?
  • Struggling with a nested where, group by and min/max in SQL
  • Simple Random Samples from a Sql database
  • Invalid column name when using dynamic SQL
  • Aurelia, navigate is not updating viewport
  • apache server reached MaxClients setting, consider…
  • Why not use tables for layout in HTML?
  • How to round an average to 2 decimal places in PostgreSQL?
  • Using multiprocessing on Image processing
  • Smart way to truncate long strings
  • How to pass 2D array (matrix) in a function in C?
  • SQL split values to multiple rows
  • How to take MySQL database backup using MySQL Workbench?
  • Fastest way to iterate over all the chars in a String
  • Improve INSERT-per-second performance of SQLite
  • Unresponsive hamburger icon in HTML
  • How can I fix MySQL error #1064?
  • How should a model be structured in MVC?
  • Is it possible to apply CSS to half of a character?
  • Backgrid filter not working in backbone app
  • How to set variables in HIVE scripts
  • Upload video files via PHP and save them in…
  • SQL find sum of entries by date including previous date
  • Error: Uncaught (in promise) TypeError: Cannot…
  • Best way to select random rows PostgreSQL
  • Managing longs and short orders like LucF PineCoders…
  • Count the Number of Tables in a SQL Server Database
  • What database does Google use?
  • How do you rotate canvas element to mouse position,…
  • Sorting A Table With Tabs & Javascript
  • JS inserting content multiple times
  • How to Update Database from Assets Folder in App
  • How to handle Vue 2 memory usage for large data (~50…
  • Cannot connect to SQL Server named instance from…
  • C++ Need help sorting a 2D string array
  • Calculate the mean by group
  • In CSS Flexbox, why are there no "justify-items" and…
  • Best practice multi language website
  • Howto: Clean a mysql InnoDB storage engine?
  • I want to create a SQLite database like in the…
  • Virtual Memory Usage from Java under Linux, too much…
  • Advantages of SQL Server 2008 over SQL Server 2005?
  • How to query for Xml values and attributes from…
  • SQL JOIN and different types of JOINs
  • How to connect to local instance of SQL Server 2008 Express
  • Creating an index on a table variable
  • set default schema for a sql query
  • Cypress finding DOM element using multiple contains
  • What are the best practices for using a GUID as a…
  • Better way to convert file sizes in Python
  • HTML table with 100% width, with vertical scroll…
  • Unable to connect to SQL Server instance remotely
  • webpack-hot-middleware throws error on HMR update -…
  • sql query to find priority jobs
  • Polymer.js Web Component Tester Failing to Find…
  • How to speed up insertion performance in PostgreSQL
  • Show all tables inside a MySQL database using PHP?
  • java.sql.SQLException: - ORA-01000: maximum open…
  • Centering in CSS Grid
  • How do I discover memory usage of my application in Android?
  • How to loop through a dataset in powershell?
  • Pass Selected Item From One Index Page To Make Title…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

NOT IN vs NOT EXISTS

Next Post:

How to get a Docker container’s IP address from the host

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error