_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 一危磯伎れ覈企蠍

create table #temp
(
	name nvarchar(255)
,	rows bigint
,	reserved varchar(100)
,	data varchar(100)
,	index_size varchar(100)
,	unused varchar(100)
)

create table #rs
(
	table_name nvarchar(500)
,	rows bigint
,	[reserved(MB)] varchar(100)
,	[data(MB)] varchar(100)
,	[index_size(MB)] varchar(100)
,	[unused(MB)] varchar(100)
)

declare 
	@tname varchar(255)
,	@sql varchar(1000); 

declare cur cursor for
select 
	table_schema + '.' + table_name tname
,	'exec sp_spaceused ''' + table_schema + '.' + table_name + '''' sql
from information_schema.tables
where table_type = 'BASE TABLE'

open cur;
fetch next from cur into @tname, @sql;
while @@FETCH_STATUS not in (-1, -2)
begin
	insert #temp exec(@sql)
	insert #rs
	select
		@tname
	,	rows 
	,	convert(bigint, replace(reserved, 'KB', '')) / 1024
	,	convert(bigint, replace(data, 'KB', '')) / 1024
	,	convert(bigint, replace(index_size, 'KB', '')) / 1024
	,	convert(bigint, replace(unused, 'KB', '')) / 1024
	from #temp
	truncate table #temp
	fetch next from cur into @tname, @sql;
end

close cur;
deallocate cur;

select * from #rs
order by convert(bigint, replace([reserved(MB)], 'KB', '')) desc

drop table #temp
drop table #rs
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

蠏碁 覓 襷 襷も朱 襷 譯 . 讌襷 蠏碁 覓 襷 kも 觜 れ 覲 . (碁 郁規ろ)