CODEWITHSUNDEEP
sqlite
starfry
starfry

Reputation: 9983

sqlite count rows of tables identified by subquery

I want to get the count of rows in every table in a Sqlite3 database. I want to avoid writing out a longhand query. I can get the list of tables like this:

SELECT name FROM sqlite_master WHERE type='table'

and I would like to use it in a subquery like this:

select count (*) from (SELECT name FROM sqlite_master WHERE type='table');

but would just return the total rows in the subquery, which isn't what I want.

How can I write a query that will list each table along with their counts?

I have seen dynamic SQL for this kind of thing but I don't think SQLite has that.

I have written a bash loop to do this but I would prefer to do it as a single query

for t in $(sqlite3 data.db "SELECT name FROM sqlite_master WHERE type='table'"); do
  echo -n "$t = "; sqlite3 data.db "SELECT COUNT(*) FROM $t;"
done

Ideas appreciated

Upvotes: 9

Views: 10128

Answers (4)

JayHawk
JayHawk

Reputation: 451

This works fine for me in sqlite3:

select count(*) from (subquery);

example:

select count(*) as differentFirstNames from (select distinct f_name from names);

Upvotes: 0

Niles
Niles

Reputation: 71

I used the following shell syntax to blindly get counts from tables in a database I was debugging.

db="database.db"

for i in `sqlite3 "$db" "SELECT name FROM sqlite_master WHERE type='table';"`;
    do echo -n $i\ \=\ ;
    sqlite3 "$db" "SELECT COUNT(*) FROM $i";
done


cols = 0
sqlite_sequence = 0
datacols = 17
hits = 0
host = 0
document = 0
admin = 2
comments = 0

Upvotes: 7

Gokulkanna Balusamy
Gokulkanna Balusamy

Reputation: 17

I hope the below code will use full for some one.

I've written the sample code in C#.net Programming language and Nuget Package is Microsoft.Data.Sqlite with version of 7.0.10

Code :

internal class Program
{
  static void Main(string[] args)
  {        
    string dbMainQuery = $" WITH x(i) AS (select name from \"main\".sqlite_master WHERE type='table') \r\n SELECT PRINTF('SELECT count(*) as NumberOfRow FROM %s;',i) as QueryInfo,i as Tablename FROM x ;";
    DataTable dtMain = new DataTable();
    DataTable dtTableCountInfo = new DataTable();
    dtTableCountInfo.Columns.Add("Id", typeof(string));
    dtTableCountInfo.Columns.Add("TableName", typeof(string));
    dtTableCountInfo.Columns.Add("TotalCount", typeof(string));
    //Get the sqlite all db data
    using (SqliteConnection sqliteConnection = new SqliteConnection("Data Source=dataStoreage.db;Pooling=True;"))
    {
        sqliteConnection.Open();
        using var transaction = sqliteConnection.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            using SqliteCommand sqliteCommand = new SqliteCommand(dbMainQuery, sqliteConnection, transaction);
            var dataReader = sqliteCommand.ExecuteReader(CommandBehavior.Default);
            dtMain.Load(dataReader);

            // Itration on the each table and get the row numbers
            if (dtMain.Rows.Count > 0)
            {
                for (int i = 0; i < dtMain.Rows.Count; i++)
                {
                    var rowInfo = dtTableCountInfo.NewRow();
                    using SqliteCommand countInfoCmd = new SqliteCommand(dtMain.Rows[i][0].ToString(), sqliteConnection, transaction);
                    rowInfo["Id"] = i + 1;
                    rowInfo["TableName"] = dtMain.Rows[i][1].ToString();
                    rowInfo["TotalCount"] = countInfoCmd.ExecuteScalar();

                    dtTableCountInfo.Rows.Add(rowInfo);
                }
            }
            transaction.Commit();
            // TO DO : do the conversion of the data table as per your needs
        }
        catch (Exception e)
        {
            transaction.RollbackAsync();
        }
    }
    Console.ReadLine();
  }
}

and the Model class :

public class TableSummryReport
{
    public string Id { get; set; }
    public string TableName { get; set; }
    public string TotalCount { get; set; }
}

Upvotes: 0

CL.
CL.

Reputation: 180162

If you want to know these values for debugging purposes, look at the output of the sqlite3_analyzer tool.

If you want to use these values in your program, you have to generate the queries dynamically in your program.

Upvotes: 4

Related Questions