T-SQL筆記30_取得特定DB(或所有DB)中tables佔有的儲存空間資訊_Cannot resolve collation conflict ... in UNION ALL operator occurring in SELECT statement column 1

- 1月 14, 2022

最近剛好研究一下it邦有個人發的問題

剛好有機會未來自己也會用的到

也就順帶研究並記錄了

議題就是

歷經千辛萬苦把這串T-SQL整理出來(其實關鍵在於怎麼跨DB陳列tables資訊)

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;