Trick for surveying a database

Often times the best place to find data in a system is to grab from the database. The APIs that many applications provide can be more bothersome and less reliable than bypassing them to go to the database.

A trick we have found useful for quickly accessing the content of a database at least with MySQL is the following code fragment:

local conn = db.connect{
   api=db.MY_SQL,
   user='root',
   password='password',
   name='test'
}

function main()
   local T = {}
   local R = conn:query('SHOW TABLES') 
   for i = 1, #R do 
      T[i]={}
      T[i].name = R[i][1]:nodeValue()
      T[i].data = ShowTable(R[i][1]:nodeValue())   
   end
   return T   
end

function ShowTable(Name)
   local R = conn:query('SELECT * FROM '..Name..' LIMIT 1')
   return R
end

This loops through all the tables in the database and queries the first row of data. It’s usually pretty fast and provides a quick browse-able tree of the database.

Ironically it often seems faster than using many of the dedicated tools available for this purpose. Less clicks required.

Hint – for Microsoft SQL Server the equivalent SQL is “SELECT name from sysobjects where xtype = “U”

See also:

• Using Database Connection Objects