SQL查看數據庫各表所占空間大小
瀏覽量:3762
有時候我們需要查看各個表內數據占用的空間大小,CRM定制,軟件開發,辦公OA,沈陽易勢科技最專業
CREATE TABLE #a (name varchar(265),
rows bigint,
reserved varchar(265),
data varchar(265),
index_size varchar(265),
unused varchar(265)
)
EXEC sp_msforeachtable 'INSERT INTO #a exec sp_spaceused''?'''
SELECT * FROM #a order by rows desc
---------或者--------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_spaceUsed_extend]
as
begin
declare @sql nvarchar(4000)
create table #tb_info(
name varchar(265),
rows bigint,
reserved varchar(265),
data varchar(265),
index_size varchar(265),
unused varchar(265)
)
declare cur_info cursor
FORWARD_ONLY
for
select name from sysobjects where xtype='u'
open cur_info
declare @name varchar(100)
fetch next from cur_info into @name
fetch next from cur_info into @name
while @@FETCH_STATUS=0
begin
select @sql='insert into #tb_info(name,rows,reserved,data,index_size,unused)'+char(13)+' exec sp_spaceused'''+@name+''''
execute(@sql)
fetch next from cur_info into @name
end
begin
select @sql='insert into #tb_info(name,rows,reserved,data,index_size,unused)'+char(13)+' exec sp_spaceused'''+@name+''''
execute(@sql)
fetch next from cur_info into @name
end
CLOSE cur_info
DEALLOCATE cur_info
DEALLOCATE cur_info
select * from #tb_info order by rows desc
drop table #tb_info
end
-----使用
exec [dbo].[sp_spaceUsed_extend]
end
-----使用
exec [dbo].[sp_spaceUsed_extend]
----
sp_spaceused 可直接查詢數據庫所占空間
上一篇:js實現簡單的彈出層效果
下一篇:使用html5畫圖的小例子