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?
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
Solution
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.
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
Note: This report isn't available when working with and Azure SQL database
Solution
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]".
Solution
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.
Solution
exec sp_spaceused N'dbo.MyTable'
For all tables ,use..(adding from the comments of Paul)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
Solution
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
Solution
For Azure I used this:
You should have SSMS v17.x+
I used;
With this, as User Sparrow has mentioned:
Open your Databases> and select Tables,
Then press key F7
You should see the row count
as:
SSMS here is connected to Azure databases
Solution
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
Solution
Marc_s' answer 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 > 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
Solution
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)) > 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)) > 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)) > 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)) > 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)) > 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)) > 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 < 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
--===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 <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC
Solution
A small change from Marc_s'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 > 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
Solution
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 < 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 > 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
Solution
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
Solution
-- 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
Solution
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.
Solution
SELECT o.name AS ObjectName,
SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB DESC;
Solution
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
Solution
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
Solution
From a command prompt using OSQL:
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
Solution
Maybe the Tables have a more partion file & must show the file order
SELECT
T1.Name AS TableName,
T5.Name AS SchemaName,
T3.partition_number AS PartionNumber,
T3.Rows AS RowsCount,
SUM(T4.total_pages) * 8 AS TotalSpaceKB,
SUM(T4.used_pages) * 8 AS UsedSpaceKB,
(SUM(T4.total_pages) - SUM(T4.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects T1 INNER JOIN
sys.indexes T2 ON T1.object_id = T2.object_id INNER JOIN
sys.partitions T3 ON T2.object_id = T3.object_id AND T2.index_id = T3.index_id INNER JOIN
sys.allocation_units T4 ON T3.partition_id = T4.container_id LEFT JOIN
sys.schemas T5 ON T1.schema_id = T5.schema_id
WHERE
T1.type='U'
GROUP BY
T1.Name, T5.Name, T3.Rows,T3.partition_number
ORDER BY
T1.Name,T3.partition_number;
Solution
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 > 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
Solution
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<>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(*) > 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
Solution
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
Solution
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 > 255
GROUP BY
t.Name, s.Name, p.Rows
) AS Result
WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC
Solution
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 > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 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
Solution
I found this query to be easy to use & 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
Solution
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 > Tasks > 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)
Solution
CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed
(
TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
insert into #tmp_table_info
select s.name+'.'+t.name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';
declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
declare @tablename varchar(200)
select @tablename=tblname
from #tmp_table_info
where id =@min
DECLARE @str VARCHAR(500)
SET @str = 'sp_spaceused '''+@tablename+''''
INSERT INTO #SpaceUsed
EXEC (@str)
set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed