{"id":158,"date":"2011-12-29T23:20:07","date_gmt":"2011-12-30T05:20:07","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=158"},"modified":"2015-12-28T16:29:51","modified_gmt":"2015-12-28T22:29:51","slug":"disk-space-get-table-sizes","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=158","title":{"rendered":"Disk Space – Get table sizes"},"content":{"rendered":"

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

Full script, below. Download script here<\/a>.<\/p>\n

<\/p>\n

\/*<\/p>\n

GRAB ALL SPACE USED BY TABLES FOR ALL DB<\/p>\n

– Exclude System Db’s<\/p>\n

– JCD 12\/28\/2011<\/p>\n

*\/<\/p>\n

USE [master]<\/span><\/p>\n

GO<\/span><\/p>\n

SET\u00a0NO COUNT <\/span><\/span>ON<\/span><\/span>;<\/span><\/span><\/p>\n

DECLARE @DBID <\/span>INT<\/span><\/span>,<\/span><\/span> @maxdbID <\/span>INT<\/span><\/span><\/p>\n

— Parameter table used to grab DB Names FROM master.dbo.sysdatabases<\/p>\n

DECLARE<\/p>\n

@DBName <\/span>Table\u00a0 <\/span><\/span>(<\/span><\/span>[id] <\/span>INT<\/span><\/span>IDENTITY <\/span><\/span>(<\/span><\/span>1<\/span>,<\/span><\/span>1<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>DBname <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>120<\/span>))<\/span><\/span><\/p>\n

— Results parameter table that returns all DB table sizes<\/p>\n

DECLARE @TablebyDB <\/span>Table<\/span><\/span>(<\/span><\/span>[Database] <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>120<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>tablename <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>200<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>rows<\/span><\/span>INT<\/span><\/span><\/p>\n

,<\/span><\/span>reserved <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>data\u00a0 <\/span>Varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>index_size <\/span>Varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>unused <\/span>varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span><\/p>\n

)<\/p>\n

— Get DB names for Server<\/p>\n

INSERT<\/span><\/span> @DBName<\/span>(<\/span><\/span>[DBname]<\/span>)<\/span><\/span><\/p>\n

SELECT<\/span><\/span> name <\/span>FROM<\/span><\/span>master<\/span><\/span>.<\/span><\/span>sys<\/span><\/span>.<\/span><\/span>databases<\/span><\/span><\/p>\n

WHERE<\/span><\/span> name <\/span>not<\/span><\/span>in<\/span><\/span>(<\/span><\/span>‘master’<\/span><\/span>,<\/span><\/span>‘tempdb’<\/span><\/span>,<\/span><\/span>‘msdb’<\/span><\/span>,<\/span><\/span>‘model’<\/span><\/span>)<\/span><\/span>— exclude system dbs<\/span><\/span><\/p>\n

and<\/span><\/span> state_desc<\/span>=<\/span><\/span>‘ONLINE’<\/span><\/span>— exclude OFFLINE DBs<\/span><\/span><\/p>\n

order <\/span><\/span>by<\/span><\/span> Name<\/span>;<\/span><\/span><\/p>\n

SELECT<\/span><\/span>\u00a0 @DBID <\/span>=<\/span><\/span>MIN<\/span><\/span>(<\/span><\/span>[id]<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>@maxdbID <\/span>=<\/span><\/span>MAX<\/span><\/span>(<\/span><\/span>[id]<\/span>)<\/span><\/span><\/p>\n

FROM<\/span><\/span> @DBName<\/span>;<\/span><\/span><\/p>\n

— Loop through the DB and get space used by table<\/p>\n

WHILE<\/p>\n

@DBID <\/span><=<\/span><\/span> @maxdbID<\/span><\/p>\n

BEGIN<\/p>\n

DECLARE<\/span><\/span>\u00a0 @DB <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>50<\/span>)<\/span><\/span>— Current DB Name<\/span><\/span><\/p>\n

,<\/span><\/span>@useStmt <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>200<\/span>)<\/span><\/span>— Build Statement fro sp_spaceused<\/span><\/span><\/p>\n

— Capure into Temp table table size data <\/span><\/span><\/p>\n

— Insert’s not allowed FROM EXEC() in to parameter tables<\/span><\/span><\/p>\n

IF<\/span><\/span>OBJECT_ID<\/span><\/span>(<\/span><\/span>‘tempdb.dbo.#results’<\/span><\/span>,<\/span><\/span>‘u’<\/span><\/span>)<\/span><\/span>is<\/span><\/span>not<\/span><\/span>null<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

DROP<\/span><\/span>TABLE<\/span><\/span> #results<\/span>;<\/span><\/span><\/p>\n

END<\/span><\/span><\/p>\n

CREATE<\/span><\/span>TABLE<\/span><\/span> #results<\/span>(<\/span><\/span>name <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>200<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>rows<\/span><\/span>INT<\/span><\/span><\/p>\n

,<\/span><\/span>reserved <\/span>VARCHAR<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>data\u00a0 <\/span>Varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>index_size <\/span>Varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/p>\n

,<\/span><\/span>unused <\/span>varchar<\/span><\/span>(<\/span><\/span>30<\/span>)<\/span><\/span><\/p>\n

)<\/span><\/span><\/p>\n

SELECT<\/span><\/span> @DB<\/span>=<\/span><\/span>RTRIM<\/span><\/span>(<\/span><\/span>DBname<\/span>)<\/span><\/span><\/p>\n

,<\/span><\/span>@useStmt <\/span>=<\/span><\/span>‘USE [‘<\/span><\/span>+<\/span><\/span>RTRIM<\/span><\/span>(<\/span><\/span>DBname<\/span>)+<\/span><\/span>‘];EXECUTE sp_msforeachtable ”execute sp_spaceused [?];”’<\/span><\/span><\/p>\n

FROM<\/span><\/span> @DBName<\/span><\/p>\n

WHERE<\/span><\/span> [id] <\/span>=<\/span><\/span> @DBID<\/span><\/p>\n

— Populate Temp table FROM sp_spaceused by table<\/span><\/span><\/p>\n

INSERT<\/span><\/span>INTO<\/span><\/span> #results<\/span><\/p>\n

Execute<\/span><\/span>(<\/span><\/span>@useStmt<\/span>)<\/span><\/span><\/p>\n

— Insert data in parameter table <\/span><\/span><\/p>\n

INSERT<\/span><\/span>INTO<\/span><\/span> @TablebyDB<\/span><\/p>\n

SELECT<\/span><\/span> @DB <\/span><\/p>\n

,<\/span><\/span>name <\/span><\/p>\n

,<\/span><\/span>rows<\/span><\/span><\/p>\n

,<\/span><\/span>reserved<\/span><\/p>\n

,<\/span><\/span>data<\/span><\/p>\n

,<\/span><\/span>index_size<\/span><\/p>\n

,<\/span><\/span>unused<\/span><\/p>\n

FROM<\/span><\/span> #results<\/span><\/p>\n

— increment id <\/span><\/span><\/p>\n

Set<\/span><\/span> @DBID <\/span>=<\/span><\/span> @DBID <\/span>+<\/span><\/span>1<\/span><\/p>\n

— Clean up <\/span><\/span><\/p>\n

IF<\/span><\/span>OBJECT_ID<\/span><\/span>(<\/span><\/span>‘tempdb.dbo.#results’<\/span><\/span>,<\/span><\/span>‘u’<\/span><\/span>)<\/span><\/span>is<\/span><\/span>not<\/span><\/span>null<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

DROP<\/span><\/span>TABLE<\/span><\/span> #results<\/span>;<\/span><\/span><\/p>\n

END<\/span><\/span><\/p>\n

END<\/p>\n

— Return results in GB<\/p>\n

SELECT<\/p>\n

@@SERVERNAME<\/span><\/span> [Instance]<\/span><\/p>\n

,<\/span><\/span>[Database]<\/span><\/p>\n

,<\/span><\/span>tablename [Table Name]<\/span><\/p>\n

,<\/span><\/span>[rows]<\/span><\/p>\n

,<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>5<\/span>),<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>3<\/span>),<\/span><\/span>REPLACE<\/span><\/span>(<\/span><\/span>reserved<\/span>,<\/span><\/span>‘KB’<\/span><\/span>,<\/span><\/span>”<\/span><\/span>))\/<\/span><\/span>1024.0<\/span>\/<\/span><\/span>1024.0<\/span>)<\/span><\/span> [Reserved GB]<\/span><\/p>\n

,<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>5<\/span>),<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>3<\/span>),<\/span><\/span>REPLACE<\/span><\/span>(<\/span><\/span>data<\/span>,<\/span><\/span>‘KB’<\/span><\/span>,<\/span><\/span>”<\/span><\/span>))\/<\/span><\/span>1024.0<\/span>\/<\/span><\/span>1024.0<\/span>)<\/span><\/span>[Data GB]<\/span><\/p>\n

,<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>8<\/span>),<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>3<\/span>),<\/span><\/span>REPLACE<\/span><\/span>(<\/span><\/span>index_size<\/span>,<\/span><\/span>‘KB’<\/span><\/span>,<\/span><\/span>”<\/span><\/span>))\/<\/span><\/span>1024.0<\/span>\/<\/span><\/span>1024.0<\/span>)<\/span><\/span> [Index Size GB]<\/span><\/p>\n

,<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>8<\/span>),<\/span><\/span>CONVERT<\/span><\/span>(<\/span><\/span>NUMERIC<\/span><\/span>(<\/span><\/span>18<\/span>,<\/span><\/span>3<\/span>),<\/span><\/span>REPLACE<\/span><\/span>(<\/span><\/span>unused<\/span>,<\/span><\/span>‘KB’<\/span><\/span>,<\/span><\/span>”<\/span><\/span>))\/<\/span><\/span>1024.0<\/span>\/<\/span><\/span>1024.0<\/span>)<\/span><\/span> [Unused GB]<\/span><\/p>\n

,<\/span><\/span>GetDate<\/span><\/span>()<\/span><\/span> [Collectiontime]<\/span><\/p>\n

FROM\u00a0 @TablebyDB <\/span><\/p>\n

ORDER BY<\/span><\/span> [Database]<\/span>,<\/span><\/span> tablename<\/span><\/p>\n

GO<\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Quick and low impact alternative to SELET COUNT(*) queries. Uses sp_spaceused to return row count and space used in GB for all online user datbases. Tested on SQL 2005, SQL 2008 and SQL 2008 R2<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"footnotes":""},"categories":[273,266,22,5],"tags":[303,315,249,35,337,340,36,295,52,336,291,342],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-t-sql","tag-dba","tag-query","tag-script","tag-select","tag-sp_msforeachtable","tag-sp_spaceused","tag-sql","tag-sql-server","tag-ssms","tag-sys-sysdatabases","tag-t-sql","tag-table"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=158"}],"version-history":[{"count":13,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":328,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions\/328"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}