Last week I found a SQL statement to find the size of rows in a database. That was helpful but I wanted to be able to run this from the command line. Sounds like a good job for the Groovy database features.





import groovy.sql.Sql



//connect to the database

def sql = Sql.newInstance('jdbc:oracle:thin:user/pass@host:1521:database')



//define the statement to run

def sizeStatement = """select table_name, num_rows, avg_row_len,

ROUND((num_rows * avg_row_len / 1048576),2) "mb_used"

from user_tables

where num_rows > 0"""



//create a date to output (not needed for SQL, just for reporting purposes

def formattedDate = String.format('%tD %

//output the results

sql.eachRow(sizeStatement) { row ->

println "${formattedDate},${row.table_name},${row.mb_used}"

}

groovy -classpath OracleThinDrivers.jar CheckSize.groovy

A nice little script. The only catch is that my database is Oracle so I need to pass in the driver jar when I run the script from the command line like this