.::: Check Table Capacity & Size Report SQL Server on LocalServer & Linked Server (Open Query) :::.

A. Access Local Server
1. Check Database_ID
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases where name = 'teguhth';

2. Check Table Capacity Report

SELECT @@SERVERNAME as [Server_name], db_name() [DB_Name], getdate() as [Date],
    --row_number() over(order by a3.name, a2.name))%2 as l1,
    a3.name AS [schemaname],
    a2.name AS [tablename],
    a1.rows as row_count,
    ((a1.reserved + ISNULL(a4.reserved,0))* 8)/1024 AS reserved_in_MB,
    (a1.data * 8)/1024 AS data_in_MB,
    ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END))/1024 * 8 AS index_size_in_MB,
    ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8)/1024 AS unused_in_MB
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  with (NOLOCK)
        WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables  with (NOLOCK) 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  with (NOLOCK)
     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 reserved_in_MB desc

3. Check Table Size Report

SELECT @@Servername as [ServerName],db_name() as [DBName],getdate() [Date],
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows as 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 with (NOLOCK)
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

B. Access LinkedServer
1. Check Database_ID
select @@servername as LocalServer, * from openquery(labvm,'select @@servername as ServerName_Link, name as dbname,database_id  from sys.databases where name = ''teguhth''')

2. Check Table Capacity Report
select @@servername as LocalServer, * from openquery(labvm,'SELECT @@SERVERNAME as ServerName_Link, db_name(''5'') as [DBName], getdate() as [Date],
    --row_number() over(order by a3.name, a2.name))%2 as l1,
    a3.name AS [schemaname],
    a2.name AS [tablename],
    a1.rows as row_count,
    ((a1.reserved + ISNULL(a4.reserved,0))* 8)/1024 AS reserved_in_MB,
    (a1.data * 8)/1024 AS data_in_MB,
    ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END))/1024 * 8 AS index_size_in_MB,
    ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8)/1024 AS unused_in_MB
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 teguhth.sys.dm_db_partition_stats ps  with (NOLOCK)
        WHERE ps.object_id NOT IN (SELECT object_id FROM teguhth.sys.tables  with (NOLOCK) 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 teguhth.sys.dm_db_partition_stats ps  with (NOLOCK)
     INNER JOIN teguhth.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 teguhth.sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN teguhth.sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N''S'' and a2.type <> N''IT''
ORDER BY reserved_in_MB desc');

3. Check Table size Report

select @@servername as LocalServer, * from openquery(labvm,'SELECT @@Servername as ServerName_Link,db_name(''5'') as [DBName],getdate() [Date],
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows as 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
    teguhth.sys.tables t with (NOLOCK)
INNER JOIN
    teguhth.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    teguhth.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    teguhth.sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    teguhth.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');