Skip to content

Instantly share code, notes, and snippets.

@vkryukov

vkryukov/database_table_sizes.R

Created March 24, 2014 19:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save vkryukov/9746813 to your computer and use it in GitHub Desktop.
Save vkryukov/9746813 to your computer and use it in GitHub Desktop.
Download ZIP
Calculate MS SQL database table sizes in R
Raw
database_table_sizes.R
tables <- SQLQuery("
select TABLE_NAME as table_name
from cs_reporting.information_schema.tables
where table_type = 'BASE TABLE'")
sizes <- rbindlist(lapply(tables$table_name, function(x) SQLQuery(paste0("sp_spaceused [", x, "]"))))
StripKB <- function(x) as.integer(str_replace_all(x, ' KB', ''))
sizes[, reserved := StripKB(reserved)]
sizes[, data := StripKB(data)]
sizes[, index_size := StripKB(index_size)]
sizes[, unused := StripKB(unused)]
write.csv(sizes, 'table_sizes.csv')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment