Listing table name(s) and record count

[marksmithhfx]

Hi, can anyone tell me how (in mySQL and SQLite) to list the names of tables and the record count for each?

Thanks

Mark

[Simon]

Hi Mark,
Here is a start

put "SELECT * FROM sqlite_master;" into tSQL
put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords

will get you the table names and columns.

Simon

[Klaus]

Hi Mark,

check "revdb_tablenames" and "revdb_recordcount" in the dictionary.

And maybe you, too, Simon


Best

Klaus

[Simon]

Wow...
Look at all those revdb_ functions!

Simon

[Klaus]

Yeah, isn't it amazing


If you like some music, check this:
http://www.youtube.com/watch?v=WJtocVmJ4ek
I'm the guy with the black bass guitar

[Simon]

Hi Klaus,
Actually last week I checked out your website.
I don't know where I got the idea but I always thought you were a musician.
Great stuff.

Simon

[marksmithhfx]

Thanks guys!

It looks like the revdb_ commands do not have a good count function for tables, so I patched one together using revDataFromQuery(). If I understand correctly revdb_recordcount() only works on a recordset ID which means a two step process (query first, then count) versus just using SQL's built in COUNT(*) function. I opted for the latter, which works, but have not tested which is faster (my database is small at the moment). revdb_tablenames() is brilliant. I am going to make a feature request to RR to modify revdb_recordcount() to work with either a recordset ID or a connection/database ID. There is precedent as revdb_ColumnNames() currently accepts either ID, with optional parameter for table name if you use the connection ID.

Your input was most helpful. Thanks to both of you!

Code: Select all

   ## list a set of table names with record counts
   put empty into field "data"
   put revdb_tablenames(gConnectID) into tdata -- get a list of the tablenames in the current db
   -- now lets get the record count for each table
   repeat for each word thisWord in tdata  -- loop through all of the table names
      put thisWord into tTablename
      put "SELECT COUNT(*) FROM " & tTableName into tSQL -- and query the record count
      put revDataFromQuery(tab, cr, gConnectID, tSQL) into tCount
      put thisWord & comma & " " & tCount & cr after field "data"  -- display results in a field
   end repeat

[marksmithhfx]

Klaus, Simon… I just wanted to confirm I've tested the code I posted above on both SQLite and mySQL and it works for both platforms. However this part of the code suggested by Simon does not work on mySQL

put "SELECT * FROM sqlite_master;" into tSQL
put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords

Which is probably obvious. Anyway, I tried substituting "information_schema" for "sqlite_master" in the above, because I can see there is a database called information_schema in my On-Rev account, but it just returns an error "revdberr,Table 'lcxxxx80_mydatabase.information_schema' doesn't exist". This may just be one of those permission kinds of things where I can access all of the tables in "lcxxxx80_mydatabase" but I am not allowed to access the database "information_schema". It is interesting that the information_schema provides lots of useful information about tables, including the number of records (rows), but not the number of fields (columns).

Mark

[marksmithhfx]

Example of output

Here's a mod to my previous post that lists table names, row and column counts and puts the result in a basic table field. Tested and working in both SQLite and mySQL. If I can attach a jpg here I'll include an example output.

Code: Select all

global gConnectionID

--| Get the table names, row and column counts from a mySQL or SQLite database file
--| and display in a basic Table Field

on mouseUp
   put empty into field "data"
   put "Table" & tab & "Rows" & tab & "Columns" & cr & cr into tdata
   put revdb_tablenames(gConnectionID) into tnames -- get a list of the tablenames in the current db
   -- now lets get the record count for each table
   repeat for each word thisTable in tnames  -- loop through all of the table names
      put thisTable into tTablename
      -- now lets get a record count
      put "SELECT COUNT(*) FROM " & tTableName into tSQL
      put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tRows
      -- now lets get a column count. 
      put "SELECT * FROM " & tTableName into tSQL
      put revQueryDatabase(gConnectionID, tSQL) into recordsetID
      put revDatabaseColumnCount(recordsetID) into tColumns
      put thisTable & tab & tRows & tab & tColumns & cr after tdata  -- ad results to tab delimited field
   end repeat
   -- display results
   set text of field "data" to tdata
end mouseUp

[Klaus]

Hi Mark,

If I understand correctly revdb_recordcount() only works on a recordset ID

yes, sorry, my fault!

As an alternative you could try:
...
put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords
...



Best

Klaus

[marksmithhfx]

Hi Mark,

put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords

Well, aren't you the clever one

Thanks.

[MaxV]

This should always work:

Code: Select all

put "SELECT max(rowid) FROM  YourTable ;" into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords
answer "There are " & tRecords & " rows in YourTable" 

[Klaus]

Hi MAkr,

Hi Mark,

put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords

Well, aren't you the clever one

not really, that was complete bullshit, since that was NOT the question at all!
The number of column names, sheesh

Here another SQL command to get the number of rows of a give table:
...
SELECT COUNT(*) FROM table_name
...

Best

Klaus

[marksmithhfx]

Example output using tabWidth 150

Well, not to completely confuse everyone, but you did actually answer my question. I was looking for a way to count the number of columns in a table and I think you quite cleverly came up with

put the num of items of revDatabaseColumnNames(tConId, "YourTable") into tNumOfRecords

Apart from the fact that you stuck the result in a funny looking variable name. But it works perfectly, and replaced the 3 lines of code I was using

put "SELECT * FROM " & thisTable into tSQL
put revQueryDatabase(gConnectionID, tSQL) into recordsetID
put revDatabaseColumnCount(recordsetID) into tColumns

and eliminated an extra query. I am hoping both in the case of revDatabaseColumnNames() and in the case of COUNT(*) (which I was already using to count the rows) that the implementer has optimized the code to just read from the file schema where this information already is sitting. No querying of the database should be required. I had not thought of using "the num of items of…" so I thought that was a pretty neat trick.

What with that and help from Jacque and Simon with the columns widths I think I've nailed this minor little problem. For me it makes a nice tidy little report that shows me what files are in the database and how big (rows, columns) they are. Nothing fancy, but it works. I'm happy. Thanks for all of your excellent contributions. A nicer bunch of people it is hard to imagine.

Mark

[Klaus]

HI Mark,

glad I could help!

Apart from the fact that you stuck the result in a funny looking variable name.

Yes, I think that one finally unsettled myself a bit in the end


Best

Klaus