use testdb Go if object_id('tempdb.dbo.#tablespaceinfo','U') is not null dro
use testdb
Go
if object_id('tempdb.dbo.#tablespaceinfo','U') is not null
drop table #tablespaceinfo
create table #tablespaceinfo (
nameinfo varchar(555),
rowsinfo bigint,
reserved varchar(255),
datainfo varchar(255),
index_size varchar(255),
unused varchar(255)
)
DECLARE @tablename varchar(255);
DECLARE Info_cursor CURSOR FOR
SELECT [name] FROM sys.tables WHERE type='U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tablespaceinfo exec sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
if object_id('tempdb.dbo.#tab','U') is not null
drop table #tab
SELECT
nameinfo
,rowsinfo
,cast(replace(reserved,' KB','') as bigint)/1024 "reserved(MB)"
,cast(replace(datainfo,' KB','') as bigint)/1024 "datainfo(MB)"
,cast(replace(index_size,' KB','') as bigint)/1024 "index_size(MB)"
,cast(replace(unused,' KB','') as bigint)/1024 "unused(MB)"
into #tab
FROM #tablespaceinfo
ORDER BY Cast(Replace(reserved,'KB','') as INT) DESC
--结束END--
本文标题: 统计数据库中表大小
本文链接: https://www.lsjlt.com/news/46613.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-13
2024-05-13
2024-05-13
2024-05-13
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0