2015 的答案
有空可以試試看還可不可行

參考~列出所有表格目前容量~
至於哪個資料表超過2GB~你在下條件篩選TotalSpaceMB容量判斷就好~@@

select *
from (
	SELECT 
		t.NAME AS TableName,
		--s.Name AS SchemaName,
		p.rows AS RowCounts,
		SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
		SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
		(SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 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
) as k
ORDER BY TotalSpaceMB desc

MSSQL我不熟，幫你查到官方的資料

可依據官方的教學來處理
https://docs.microsoft.com/zh-tw/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15

不過其實幾乎所有的SQL應用。都會有一個公用的緩存表或是儲檔資料可供查尋。
可能會存在些許的誤差。

我早期曾經因為誤差偏差太大。我是利用了內部指令(DIR)來針對表的目錄來統計容量。
再轉成JSON格式來給程式應用。

以下幾個方法用T-SQL 來查詢SQL Server中資料庫大小，其中包含資料庫中Table的部份，看看有沒有可以參考利用的！
https://database.guide/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql/

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-ver15

這裡有個函數,搭配使用.

https://docs.microsoft.com/zh-tw/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15

不太專業的回答來了

USE MYDATABASE
GO 
 
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
  SUM(a.total_pages) * 8   ;
GO

Step1.先有辦法取得所有資料庫名稱列表
首先我們要先有辦法取得所有資料庫名稱列表

--declare @get_all_database_tables nvarchar(max);
--set @get_all_database_tables = (select STUFF((
--	select ','+name FROM master.dbo.sysdatabases for xml path('')
--),1,1,'') as all_database_name)
--print @get_all_database_tables;

declare @tb_tables table (table_name nvarchar(50)) 
insert into @tb_tables  select name FROM master.dbo.sysdatabases
select * from @tb_tables

Step2.針對特定DB獲取容量
參考原PO 分享的文章
Get size of all tables in database
https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database/7892349

SELECT 
    t.NAME AS TableName,
    --s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 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.is_ms_shipped = 0
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Step3.補上目標DB在sys.tables之前

再來我們都知道所謂的

select * from sys.tables --可以列出Database中所有Table物件。
--其餘sys.indexes,sys.partitions,sys.allocation_units,sys.schemas也是相關資料表
--最終目標是執行出來可幫我們陳列所有db中各自所有table

這邊前面其實有省略

select * from [哪個目標DB].sys.tables

所以最終要串出來的SQL statement就是下面這一串模板
每個再去針對target_db這個字串做取代

SELECT 
    t.NAME AS TableName,
    --s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM 
    target_db.sys.tables t
INNER JOIN      
    target_db.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    target_db.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    target_db.sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    target_db.sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

declare @tb_databases table (s_dbid int ,s_db_name nvarchar(50)) 
insert into @tb_databases  select dbid,name FROM master.dbo.sysdatabases
--select * from @tb_databases
declare @RowCnt BIGINT = 0;
set @RowCnt = (select COUNT(*)  'RowCount' from @tb_databases)
--print @RowCnt

declare @IdxRound BIGINT = 1;

while @IdxRound <= @RowCnt
begin	
	--print @IdxRound	
	declare @s_db_name nvarchar(max);
	set @s_db_name = (select s_db_name from @tb_databases where s_dbid=@IdxRound)
	--print @s_db_name

    --再把step2.程式的block貼近來然後每個sys前綴再去兜對應DB名稱即可


	set @IdxRound = @IdxRound + 1
end

最終SQL

--declare @sql nvarchar(max);
declare @tb_databases table (s_dbid int ,s_db_name nvarchar(50)) 
insert into @tb_databases  select dbid,name FROM master.dbo.sysdatabases
--select * from @tb_databases
declare @RowCnt BIGINT = 0;
set @RowCnt = (select COUNT(*)  'RowCount' from @tb_databases)
--print @RowCnt

declare @IdxRound BIGINT = 1;

while @IdxRound <= @RowCnt
begin	
	--print @IdxRound	
	declare @s_db_name nvarchar(max);
	set @s_db_name = (select s_db_name from @tb_databases where s_dbid=@IdxRound)
	--print @s_db_name

	declare @sql_tmp nvarchar(max);
	set @sql_tmp = N'SELECT 
			t.NAME AS TableName,
			--s.Name AS SchemaName,
			p.rows AS RowCounts,
			SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, 
			SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, 
			(SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
		FROM 
			target_db.sys.tables t
		INNER JOIN      
			target_db.sys.indexes i ON t.OBJECT_ID = i.object_id
		INNER JOIN 
			target_db.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
		INNER JOIN 
			target_db.sys.allocation_units a ON p.partition_id = a.container_id
		LEFT OUTER JOIN 
			target_db.sys.schemas s ON t.schema_id = s.schema_id
		WHERE 
			t.is_ms_shipped = 0
		GROUP BY 
			t.Name, s.Name, p.Rows
		ORDER BY 
			t.Name ';
	--print REPLACE(@sql_tmp,'target_db',@s_db_name)
	set @sql_tmp = REPLACE(@sql_tmp,'target_db',@s_db_name);
	exec sp_executesql @sql_tmp;
	set @IdxRound = @IdxRound + 1
end

最終在弄union all兜出SQL即可
如果不想查出來很多張table