{"id":713,"date":"2008-11-05T13:46:45","date_gmt":"2008-11-05T17:46:45","guid":{"rendered":"http:\/\/wehuberconsultingllc.com\/wordpress\/2008\/11\/05\/how-do-i-run-sp_spaceused-for-all-tables-in-a-database-technet-forums\/"},"modified":"2008-11-05T16:02:37","modified_gmt":"2008-11-05T20:02:37","slug":"how-do-i-run-sp_spaceused-for-all-tables-in-a-database-technet-forums","status":"publish","type":"post","link":"https:\/\/wehuberconsultingllc.com\/wordpress\/2008\/11\/05\/how-do-i-run-sp_spaceused-for-all-tables-in-a-database-technet-forums\/","title":{"rendered":"How do I run sp_spaceused for all tables in a database? &#8211; TechNet Forums"},"content":{"rendered":"<p>I was looking for a way to list the size of all the tables in database again and I found this nice bit of code. I made a small change on the select statement to create a calculated integer field so I could get a nice descending sort on size. It worked for me!<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">    \r\nselect 'Database Name: ', db_name()\r\n\r\nset nocount on\r\n\r\nif exists(select name from tempdb..sysobjects where name='##tmp')\r\n\r\ndrop table ##tmp\r\n\r\ncreate table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))\r\n\r\ngo\r\n\r\ndeclare @tblname varchar(50)\r\n\r\ndeclare tblname CURSOR for select name from sysobjects where xtype='U'\r\n\r\nopen tblname\r\n\r\nFetch next from tblname into @tblname\r\n\r\nWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\tinsert into ##tmp\r\n\texec sp_spaceused @tblname\r\n\tFETCH NEXT FROM tblname INTO @tblname\r\n\tEND\r\n\r\nCLOSE tblname\r\n\r\ndeallocate tblname\r\n\r\ngo\r\n\r\nselect \r\n\tnam Table_Name\r\n\t,rows Total_Rows\r\n\t,res Total_Table_Size\r\n\t,data Data_size\r\n\t,ind_sze Index_Size\r\n\t,unsed Unused_Space\r\n\t,CAST(replace(res,'KB','') as int) as Total_Table_Size_KB\r\nfrom ##tmp\r\nORDER BY Total_Table_Size_KB desc\r\n\r\ndrop table ##tmp\r\n<\/pre>\n<hr align=\"left\" width=\"25%\" size=\"1\" \/> &#8211;Vidhya Saga  <\/p>\n<p><a href=\"http:\/\/forums.microsoft.com\/TechNet\/ShowPost.aspx?PostID=2286237&amp;SiteID=17\">How do I run sp_spaceused for all tables in a database? &#8211; TechNet Forums<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was looking for a way to list the size of all the tables in database again and I found this nice bit of code. I made a small change on the select statement to create a calculated integer field so I could get a nice descending sort on size. It worked for me! select &hellip; <a href=\"https:\/\/wehuberconsultingllc.com\/wordpress\/2008\/11\/05\/how-do-i-run-sp_spaceused-for-all-tables-in-a-database-technet-forums\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;How do I run sp_spaceused for all tables in a database? &#8211; TechNet Forums&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6],"tags":[132,66],"class_list":["post-713","post","type-post","status-publish","format-standard","hentry","category-sbs2k-sbs2k3","tag-sbs2k-sbs2k3","tag-sqlserver"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4iN3d-bv","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts\/713","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/comments?post=713"}],"version-history":[{"count":5,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts\/713\/revisions"}],"predecessor-version":[{"id":714,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts\/713\/revisions\/714"}],"wp:attachment":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/media?parent=713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/categories?post=713"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/tags?post=713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}