SQL server table space used

SQL Server table space used

                                            Recently I have plan to publish some of T-SQL scripts to find table allocation space or space used. Initially i want to show how to find space in single table if you want.   

sp_spaceused '<Your Table Name>';

All list of tables,

--Partition Tables
SELECT
      s.name                        [Schema_Name],
      t.name                        Table_Name,
      i.name                        Index_Name,
      ds.name                        Partition_Scheme,  
      p.partition_number            Partition_Number,
      fg.name                        File_Group_Name,
      p.[rows]                        Row_Count,
      (au.total_pages * 8)            Total_Space_KB,
      (au.used_pages * 8)            Used_Space_KB
FROM    sys.partitions    p INNER JOIN
        sys.indexes    i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id INNER JOIN
        sys.tables t ON    t.[object_id] = i.[object_id] INNER JOIN
        sys.data_spaces    ds ON ds.data_space_id = i.data_space_id INNER JOIN
        sys.partition_schemes    ps ON ps.data_space_id = ds.data_space_id INNER JOIN
        sys.partition_functions pf ON pf.function_id = ps.function_id INNER JOIN
        sys.destination_data_spaces  dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number INNER JOIN
        sys.filegroups fg ON fg.data_space_id = dds.data_space_id INNER JOIN
        sys.allocation_units au ON p.partition_id = au.container_id LEFT OUTER JOIN
        sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE OBJECTPROPERTY(p.[object_id], 'ISMSShipped') = 0
ORDER BY s.name,t.name; 


--Non Partition Tables

SELECT
    s.name                                            [Schema_Name],
    t.name                                            Table_Name,
    fg.name                                            File_Group_Name,
    p.partition_number                                Partition_ID,
    p.[rows]                                        Row_Counts,
    SUM(au.total_pages) * 8                            Total_Space_KB,
    SUM(au.used_pages) * 8                            Used_Space_KB,
    (SUM(au.total_pages) - SUM(au.used_pages)) * 8    Unused_Space_KB
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 au ON p.partition_id = au.container_id INNER JOIN
    sys.data_spaces ds ON ds.data_space_id = i.data_space_id INNER JOIN
    sys.filegroups fg ON fg.data_space_id = i.data_space_id LEFT OUTER JOIN
    sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE OBJECTPROPERTY(p.[object_id], 'ISMSShipped') = 0
GROUP BY s.name,t.name,fg.name,p.partition_number,p.[rows]
ORDER BY s.name,t.name;  

--file space info

SELECT
b.groupname [File Group],
name [LogicalFilename],
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
[Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),
ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
[Space Used (MB)],
CONVERT (Decimal(15,2),
ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
[Available Space (MB)]
FROM    sysfiles a (NOLOCK)INNER JOIN
        sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

    

 Above script get some help to file table wise space and file group space allocation.