Query specific column on entire tables in a database

Question

I have a SQLite database contains several tables which some of them (not all) have a specific column called 'attachment'. I wonder if there is a query that get values of the 'attachment' column from all the tables which have this column. I can get all tables' name from the database, and then, query all tables individually. But I think there must be a single query to do this.

Answer 1

You can get the values of every attachment field in every table that has this field defined using the GetSchema method on the connection to find all the relevant tables then you can use the sql stament UNION to extract in a single command all the attachments values.

This should be the code using only standard ADO.NET commands and methods:

using(SQLiteConnection cnn = new SQLiteConnection(@"Data Source=E:\\temp\\mydb.db;Version=3"))
{
    cnn.Open();
    // We want all columns with the name as "attachment"
    DataTable dt = cnn.GetSchema("COLUMNS", new string[] {null, null, null, "attachment"});

   // Now we prepare the single commands that extract the attachments value
   // from every row returned by the previous query.
   // The TABLE_NAME field contains the name of the relevant table
    var s = dt.AsEnumerable().Select(x => $"SELECT attachment FROM [{x.Field<string>("TABLE_NAME")}]");

    // We join together the single commands separating them with the UNION statement
    string command = string.Join(" UNION ", s);
    
    // Finally we can construct and execute a command loading a datatable
    // with all the attachements values from every table.
    SQLiteCommand cmd = new SQLiteCommand(command, cnn);
    dt = new DataTable();
    dt.Load(cmd.ExecuteReader());

    // Here you can work on the single field in the _dt_ table 
    ....
}

Note, UNION will extract a distinct value for each attachment. This means that if you have two attachments with the same name, it will be listed only one time. If you want to keep everything then change UNION to UNION ALL (with spaces before and after the statement)

Answer 2

You can use SQLite code to get the sql SELECT statement as a string and execute it to get all the values of the column attachment in all tables of the database:

SELECT GROUP_CONCAT('SELECT ' || pti.name || ' FROM ' || sm.name, ' UNION ALL ') sql
FROM sqlite_master sm CROSS JOIN pragma_table_info(sm.name) pti
WHERE sm.type = 'table' AND pti.name = 'attachment'; 

The above query returns a resultset with only 1 row and 1 column aliased as sql with a value of a string like this:

SELECT attachment FROM table1 
UNION ALL 
SELECT attachment FROM table2 
UNION ALL 
SELECT attachment FROM table4

You can change UNION ALL to UNION inside the function GROUP_CONCAT(), depending on your requirement.

See a simplified demo.