{"id":6686,"date":"2018-05-21T12:07:01","date_gmt":"2018-05-21T17:07:01","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=6686"},"modified":"2018-05-21T12:07:01","modified_gmt":"2018-05-21T17:07:01","slug":"getting-sql-server-table-size-with-powershell","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2018\/05\/21\/getting-sql-server-table-size-with-powershell\/","title":{"rendered":"Getting SQL Server table size with Powershell"},"content":{"rendered":"<pre>\r\n<#\r\n----------------------------------------------------------------------------------------------------------------\r\nAuthor:     ******** Enterprise Architecture\r\nDate:       May 5, 2018\r\nPurpose:    List table sizes\r\nRevisions:  Initial \u2013 SDH 2018-05-05\r\nTo-do:      -\r\n----------------------------------------------------------------------------------------------------------------\r\n#>\r\n\r\n$conn = New-Object System.Data.SqlClient.SqlConnection\r\n$conn.ConnectionString = \"Server=********\\NCRWO;database=NCRWO_TransactionLog;Integrated Security=true;MultipleActiveResultSets=true\"\r\n$conn.Open()\r\n\r\n$cmd = New-Object System.Data.SqlClient.SqlCommand\r\n$cmdInner = New-Object System.Data.SqlClient.SqlCommand\r\n\r\n$cmd.Connection = $conn\r\n$cmdInner.Connection = $conn\r\n\r\n$cmd.CommandText = \"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'\"\r\n  \r\n$swReader = $cmd.ExecuteReader()\r\nwhile ($swReader.Read()) {\r\n  $cmdInner.CommandText = \"exec sp_spaceused [\" + $swReader['table_name'] + \"]\"\r\n  $swInnerReader = $cmdInner.ExecuteReader()\r\n  while ($swInnerReader.Read()) {\r\n    $tmp=[int]$swInnerReader['reserved'].replace(\" KB\",\"\") \r\n    $swInnerReader[\"name\"] + \",\" + $swInnerReader['rows'].toString() + \",\" + $tmp.toString() + \",\" + $swInnerReader['data'] + \",\" + $swInnerReader['index_size'] + \",\" + $swInnerReader['unused']| Out-File 'C:\\USERS\\ADM-SHOWARD\\DOWNLOADS\\edrsizelog.csv' -Append\r\n  }\r\n  $swInnerReader.Close()\r\n}\r\n$swReader.Close();\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>$conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = &#8220;Server=********\\NCRWO;database=NCRWO_TransactionLog;Integrated Security=true;MultipleActiveResultSets=true&#8221; $conn.Open() $cmd = New-Object System.Data.SqlClient.SqlCommand $cmdInner = New-Object System.Data.SqlClient.SqlCommand $cmd.Connection = $conn $cmdInner.Connection = $conn $cmd.CommandText = &#8220;SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=&#8217;BASE TABLE'&#8221; $swReader = $cmd.ExecuteReader() while ($swReader.Read()) { $cmdInner.CommandText =&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2018\/05\/21\/getting-sql-server-table-size-with-powershell\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,55,34,30],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6686"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=6686"}],"version-history":[{"count":3,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6686\/revisions"}],"predecessor-version":[{"id":6701,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/6686\/revisions\/6701"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=6686"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=6686"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=6686"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}