Disk Space – Get table sizes

Below is a script  I use to determine table size.  Basically, it walks through the user databases and for each table.  The information is converted in GB. It’s a pretty simple use of MS sp_spaceused with sp_msforeachtable. More info on sp_spaceused.

Full script, below. Download script here.

/*

GRAB ALL SPACE USED BY TABLES FOR ALL DB

– Exclude System Db’s

– JCD 12/28/2011

*/

USE [master]

GO

SET NO COUNT ON;

DECLARE @DBID INT, @maxdbID INT

— Parameter table used to grab DB Names FROM master.dbo.sysdatabases

DECLARE

@DBName Table  ([id] INTIDENTITY (1,1)

,DBname VARCHAR(120))

— Results parameter table that returns all DB table sizes

DECLARE @TablebyDB Table([Database] VARCHAR(120)

,tablename VARCHAR(200)

,rowsINT

,reserved VARCHAR(30)          

,data  Varchar(30)            

,index_size Varchar(30)       

,unused varchar(30)

)

— Get DB names for Server

INSERT @DBName([DBname])

SELECT name FROMmaster.sys.databases

WHERE name notin(‘master’,‘tempdb’,‘msdb’,‘model’)— exclude system dbs

and state_desc=‘ONLINE’— exclude OFFLINE DBs

order by Name;

SELECT  @DBID =MIN([id])

,@maxdbID =MAX([id])

FROM @DBName;

— Loop through the DB and get space used by table

WHILE

@DBID <= @maxdbID

BEGIN

DECLARE  @DB VARCHAR(50)— Current DB Name

,@useStmt VARCHAR(200)— Build Statement fro sp_spaceused

— Capure into Temp table table size data

— Insert’s not allowed FROM EXEC() in to parameter tables

IFOBJECT_ID(‘tempdb.dbo.#results’,‘u’)isnotnull

BEGIN

DROPTABLE #results;

END

CREATETABLE #results(name VARCHAR(200)

,rowsINT

,reserved VARCHAR(30)          

,data  Varchar(30)            

,index_size Varchar(30)       

,unused varchar(30)

)

SELECT @DB=RTRIM(DBname)

,@useStmt =‘USE [‘+RTRIM(DBname)+‘];EXECUTE sp_msforeachtable ”execute sp_spaceused [?];”’

FROM @DBName

WHERE [id] = @DBID

— Populate Temp table FROM sp_spaceused by table

INSERTINTO #results

Execute(@useStmt)

— Insert data in parameter table

INSERTINTO @TablebyDB

SELECT @DB

,name

,rows

,reserved

,data

,index_size

,unused

FROM #results

— increment id

Set @DBID = @DBID +1

— Clean up

IFOBJECT_ID(‘tempdb.dbo.#results’,‘u’)isnotnull

BEGIN

DROPTABLE #results;

END

END

— Return results in GB

SELECT

@@SERVERNAME [Instance]

,[Database]

,tablename [Table Name]

,[rows]

,CONVERT(NUMERIC(18,5),CONVERT(NUMERIC(18,3),REPLACE(reserved,‘KB’,))/1024.0/1024.0) [Reserved GB]

,CONVERT(NUMERIC(18,5),CONVERT(NUMERIC(18,3),REPLACE(data,‘KB’,))/1024.0/1024.0)[Data GB]

,CONVERT(NUMERIC(18,8),CONVERT(NUMERIC(18,3),REPLACE(index_size,‘KB’,))/1024.0/1024.0) [Index Size GB]

,CONVERT(NUMERIC(18,8),CONVERT(NUMERIC(18,3),REPLACE(unused,‘KB’,))/1024.0/1024.0) [Unused GB]

,GetDate() [Collectiontime]

FROM  @TablebyDB

ORDER BY [Database], tablename

GO

 

 

Tiny URL for this post:
 

Share the joy

Comments are closed.