SQL查看数据库各表所占空间大小
浏览量：3676
有时候我们需要查看各个表内数据占用的空间大小，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画图的小例子