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:
Comments are closed.