Master of None

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