May 25, 2017 at 10:42 am
Hello All,
How can i get the size of all of my tables in my single DB and the physical file location (.mdf,.ndf) of each table?
May 25, 2017 at 2:29 pm
89netanel - Thursday, May 25, 2017 10:42 AMHello All,
How can i get the size of all of my tables in my single DB and the physical file location (.mdf,.ndf) of each table?
Not sure what you are looking for in terms of size but maybe something like this would work for you:
SELECT
t.name AS TableName,
p.rows AS [RowCount],
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
f.name AS [FileGroup],
d.physical_name AS PhysicalFileName
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 sys.filegroups f
ON f.data_space_id = i.data_space_id
INNER JOIN sys.database_files d
ON f.data_space_id = d.data_space_id
GROUP BY
t.name, p.rows, f.name, d.physical_name
ORDER BY
t.name
Sue
May 25, 2017 at 2:48 pm
you can try something like this:
--nonpartitioned
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, p.partition_number, ps.used_page_count * 8 AS total_KB,
ds.name AS data_space, f.name AS logical_file, f.physical_name
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
INNER JOIN sys.partitions p ON ps.partition_id = p.partition_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN sys.database_files f ON ds.data_space_id = f.data_space_id
WHERE i.index_id IN (0,1)
UNION ALL
--partitioned
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, p.partition_number, ps.used_page_count * 8 AS total_KB,
ds.name AS data_space, f.name AS logical_file, f.physical_name
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
INNER JOIN sys.partitions p ON ps.partition_id = p.partition_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN sys.destination_data_spaces dds ON ds.data_space_id = dds.partition_scheme_id AND p.partition_number = dds.destination_id
INNER JOIN sys.database_files f ON dds.data_space_id = f.data_space_id
WHERE i.index_id IN (0,1)
ORDER BY SCHEMA_NAME(t.schema_id), t.name, p.partition_number
May 26, 2017 at 5:05 am
89netanel - Thursday, May 25, 2017 10:42 AMHello All,
How can i get the size of all of my tables in my single DB and the physical file location (.mdf,.ndf) of each table?
This should get you going
USE [yourdb]
GO
--=======================================
--Locate data files on the filesystem
--=======================================
SELECTDB_NAME()
, name
, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('yourdb')
--====================================================
--get index sizes and compression - sql 2008 on
--====================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT DB_NAME() AS DBName
, s.name AS SchemaName
, OBJECT_NAME(o.OBJECT_ID) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, CASE i.[type]
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'Clustered'
WHEN 2 THEN 'NonClustered'
WHEN 3 THEN 'XML'
WHEN 4 THEN 'Spatial'
END AS IndexType
, i.is_disabled AS IsDisabled
, i.data_space_id
, CASE
WHEN i.data_space_id > 65600 THEN ps.name
ELSE f.name
END AS FG_or_PartitionName
, p.partition_number AS PartitionNo
, p.[rows] AS [RowCnt]
, p.data_compression_desc AS CompressionType
, au.type_desc AS AllocType
, au.total_pages / 128 AS TotalMBs
, au.used_pages / 128 AS UsedMBs
, au.data_pages / 128 AS DataMBs
FROM sys.indexes i
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] IN (1,3) THEN p.hobt_id
WHEN au.[type] = 2 THEN p.partition_id
END = au.container_id
WHERE o.is_ms_shipped <> 1
ORDER BY p.rows desc
OPTION (RECOMPILE);
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply