Finding tables having columntype BLOB in sqlite

Question

How can I find the tables having column Blob type in Sqlite. I need to get the table names from which I get the column blob type and then want to see the total no. of records where the blob is not empty.

Answer 1

If you wanted tables that have a column defined as a blob then you could use

SELECT * FROM sqlite_master WHERE sql LIKE '%blob%';

as the basis for determining the tables. e.g. this could return results such as :-

However, this does not necessarily find all values that are stored as blobs. This is because with the exception of the rowid column or an alias thereof, any type of value (blob included) can be stored in any column.

e.g. consider the following :-

DROP TABLE IF EXISTS not_a_blob_table;
CREATE TABLE IF NOT EXISTS not_a_blob_table (col1 TEXT, col2 INTEGER, col3 REAL, col4 something_or_other);
INSERT INTO not_a_blob_table VALUES
    ('test text',123,123.4567,'anything'), -- Insert using types as defined
  (x'00',x'12',x'34',x'1234567890abcdefff00') -- Insert with all columns as blobs
;
SELECT typeof(col1),typeof(col2),typeof(col3),typeof(col4) FROM not_a_blob_table;

This results in :-

If you want to find all blobs then you would need to process all columns from all rows of all tables based upon a check for the column type. This could perhaps be based upon :-

SELECT typeof(col1),typeof(col2),typeof(col3),typeof(col4),* FROM not_a_blob_table 
WHERE typeof(col1) = 'blob' OR typeof(col2) = 'blob' OR typeof(col3) = 'blob' OR typeof(col4) = 'blob';

Using the table above this would result (only the 2nd row has blobs) in :-

A further complication is what you mean by not empty, null obviously. However what about x'00'? or if you used a default of zeroblob(0) ?.

• zeroblob(N)

  • The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface.

If null though then this wouldn't have a type of blob, instead it's type would be null, which could complicate matters if checking for all values stored as blobs.

You may wish to consider having a look at the code from Are there any methods that assist with resolving common SQLite issues? as this could well be the basis for what you want.

You also wish to have a look at typeof(X) and zeroblob(N).