Table size in statistical database

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

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