iT邦幫忙

3

如何找出SQL Server 「全部」資料庫大於 2 GB 的Table

不好意思打擾各位><
我在寫一個stored procedure讓DB產出各種狀態的報表(html),
其中有一個項目就是要列出所有DB內大小超過2GB的Table。

有在很多地方找到找出當下執行語法資料庫大於2GB的Table,
但是都沒有找到可以一次查詢 「全部」資料庫Table大小的語法,
雖然我知道可以用USE DB_Name去指定資料庫,可是因為產出報表的TSQL不讓我用USE DB_Name,且我不止要查單一資料庫所有Table大小,所以就沒用這個方法。

請問有大大知道要怎麼用TSQL一次找出全部DB的Table大小嗎?
我目前寫的部分sp如下,只能產出當下執行DB的大於2GB Table大小,
請各位大神幫助我,謝謝!

SELECT 
	@DatabaseServerInformation =  @DatabaseServerInformation + 
	'</table>
	<p><font face="Verdana" size="4">Tables larger than 2GB</font></p>
	<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="24%" border="1">
	  <tr>
		<td width="50%" bgColor="#E5BFBC" height="15"><b>
		<font face="Verdana" size="1" color="#000000">TableName</font></b></td>
		<td width="50%" bgColor="#E5BFBC" height="15"><b>
		<font face="Verdana" size="1" color="#000000">indexName</font></b></td>
		<td width="50%" bgColor="#E5BFBC" height="15"><b>
		<font face="Verdana" size="1" color="#000000">RowCounts</font></b></td>
		<td width="70%" bgColor="#E5BFBC" height="15"><b>
		<font face="Verdana" size="1" color="#000000">TotalSpaceMB</font></b></td>
	  </tr>
	<p style="margin-top: 1; margin-bottom: 0">&nbsp;</p>'
	
SELECT 
	@DatabaseServerInformation =  @DatabaseServerInformation +   
	'<tr><td><font face="Verdana" size="1">' + Convert(varchar, t.NAME ) +'</font></td>' +
	'<td><font face="Verdana" size="1">' + Convert(varchar, i.name ) +'</font></td>' +
	'<td><font face="Verdana" size="1">' + Convert(varchar, sum(p.rows)) +'</font></td>' +
	'<td><font face="Verdana" size="1">' + Convert(varchar,CONVERT(INT,(sum(a.total_pages) * 8) / 1024)+'</font></td></tr>' 

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
WHERE t.NAME NOT LIKE 'dt%' 
AND i.OBJECT_ID > 255 
AND i.index_id <= 1
AND a.total_pages > 256000

GROUP BY t.NAME, i.object_id, i.index_id, i.name
ORDER BY object_name(i.object_id)
4
海綿寶寶
iT邦大神 1 級 ‧ 2022-01-13 14:37:03
最佳解答

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

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 15:37:01 檢舉

太感謝您了!!! sp已經跑成功了!
終於可以安心下班了XD

1
純真的人
iT邦大師 1 級 ‧ 2022-01-13 14:33:38

參考~列出所有表格目前容量~
至於哪個資料表超過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

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 14:38:24 檢舉

這個好像也是只能找出一個DB的所有表格的容量~
請問有辦法一次找出全部的DB的所有表格容量嗎? 因為我都統一把sp放在一個DB上跑,這樣只會找那個DB :<
謝謝您!~

1

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格式來給程式應用。

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 15:53:20 檢舉

謝謝您的用心回覆! 很抱歉因為順序問題沒辦法選您當最佳解答
再次謝謝您!

1
Gary
iT邦研究生 1 級 ‧ 2022-01-13 15:19:45

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

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 16:20:28 檢舉

謝謝您的回覆!

3
一級屠豬士
iT邦大師 1 級 ‧ 2022-01-13 15:31:03
lulu_meat iT邦新手 3 級 ‧ 2022-01-13 15:58:15 檢舉

謝謝您的回覆! 不知道為什麼每次我想邀請您回答,系統都不讓我邀,但是您都會來回覆問題,謝謝您!

https://ithelp.ithome.com.tw/upload/images/20220113/20001787MQWPPVEb9r.png

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 16:21:09 檢舉

我下次把無法邀請的過程螢幕錄影PO上來好了

1
japhenchen
iT邦超人 1 級 ‧ 2022-01-13 16:06:12

不太專業的回答來了

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


lulu_meat iT邦新手 3 級 ‧ 2022-01-13 16:21:50 檢舉

謝謝您的回覆!!~

2
Samuel
iT邦新手 1 級 ‧ 2022-01-13 16:13:55

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

lulu_meat iT邦新手 3 級 ‧ 2022-01-13 16:20:17 檢舉

超詳細的...我現在是跪著看完、跪著回覆,非常感謝您!

Samuel iT邦新手 1 級 ‧ 2022-01-13 17:03:09 檢舉

不會教學相長
只是剛好想之後也可能會遇到順便研究
原po的鐵人文章都很有趣喔

添加union all寫法(應該還有更好更穩定的寫法待有空再研究)

declare @sql nvarchar(max);
set @sql = '';--記得初始化
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;
--@RowCnt
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 * 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 
			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
			) as tb_result  ';
	--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;
	
	if @IdxRound = @RowCnt
		begin
			set @sql = @sql + @sql_tmp;	
		end
	else
		begin
			set @sql = @sql + @sql_tmp + ' UNION ALL ';	
		end	
	set @IdxRound = @IdxRound + 1
end
--print @sql;
--SELECT CAST('<![CDATA[' + CAST(@sql as nvarchar(max)) + ']]>' as xml) --SSMS 顯示有文字長限制用這複製到note才可以看完整的
exec sp_executesql @sql;



我要發表回答

立即登入回答