sqlserver計算テーブル使用サイズsql
5837 ワード
declare @id NVARCHAR(100)
create table #spt_space
(
[name] NVARCHAR(50) null,
[rows] int null,
[reserved] NVARCHAR(50) null,
[data] NVARCHAR(50) null,
[index_size] NVARCHAR(50) null,
[unused] NVARCHAR(50) null
)
set nocount on
declare c_tables cursor for
select name from sysobjects where xtype = 'U'
open c_tables fetch next from c_tables into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space ([name],[rows],reserved,data,index_size,unused)
EXEC sp_spaceused @id
fetch next from c_tables into @id
end
SELECT * FROM(
SELECT *,CAST(REPLACE(reserved,'KB','') AS float(3))/(1024*1024) AS reservedindex FROM #spt_space
) c
order by c.reservedindex desc
drop table #spt_space
close c_tables
deallocate c_tables