SQL查看數(shù)據(jù)庫各表所占空間大小
瀏覽量：3816
有時候我們需要查看各個表內(nèi)數(shù)據(jù)占用的空間大小，CRM定制，軟件開發(fā)，辦公OA，沈陽易勢科技最專業(yè)
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 可直接查詢數(shù)據(jù)庫所占空間
下一篇：使用html5畫圖的小例子