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

ITPub博客

SQL Server统计数据库中表大小

最新文章