Suggestion a new dot command (.map)
(1) By anonymous on 2021-01-29 15:38:37 [link]
Suggestion for a new CLI dot command (may be .map) to produce the table below: 1.to promote at-a-glance familiarisation with any given database database. 2.to assist in the enumeration of column names in SQL statements 3.(may be even an aid to manual version control over time) <b>Column Name</b>|<b>Description</b> AtDate|Current timestamp dbname|Fully qualified database name tid|Ordinal position of table tbl_name|Table name tbl_type|Table type [table or view] cid|Ordinal position of column in table col_name|Column name col_type|Colum type nullable|Column acceps null? dflt_value|Column default value pk|Primary key? ``` ```` <b>AtDate</b> | |<b>dbname</b> |<b>tid</b>|<b>tbl_name</b> |<b>tbl_type</b>|<b>cid</b>|<b>col_name</b> |<b>col_type</b> |<b>nullable</b>|<b>dflt_value</b>|<b>pk</b> 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 12|Table1 |table | 1|Field1 |varchar(255)| 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 12|Table1 |table | 2|Field2 |varchar(255)| 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 12|Table1 |table | 0|ID |integer | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 7|USStateAb |table | 0|Abbreviation |nvarchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 7|USStateAb |table | 2|Classification|nvarchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 7|USStateAb |table | 1|State |nvarchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 4|address |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 2|au_fname |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 0|au_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 1|au_lname |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 5|city |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 8|contract |bit | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 3|phone |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 6|state |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 9|authors |table | 7|zip |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 0|discounts |table | 4|discount |decimal | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 0|discounts |table | 0|discounttype |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 0|discounts |table | 3|highqty |smallint | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 0|discounts |table | 2|lowqty |smallint | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 0|discounts |table | 1|stor_id |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 0|emp_id |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 1|fname |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 7|hire_date |datetime | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 4|job_id |smallint | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 5|job_lvl |tinyint | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 3|lname |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 2|minit |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 11|employee |table | 6|pub_id |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 1|jobs |table | 1|job_desc |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 1|jobs |table | 0|job_id |smallint | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 1|jobs |table | 3|max_lvl |tinyint | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 1|jobs |table | 2|min_lvl |tinyint | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 2|publishers |table | 2|city |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 2|publishers |table | 4|country |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 2|publishers |table | 0|pub_id |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 2|publishers |table | 1|pub_name |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 2|publishers |table | 3|state |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 3|roysched |table | 2|hirange |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 3|roysched |table | 1|lorange |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 3|roysched |table | 3|royalty |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 3|roysched |table | 0|title_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 2|ord_date |datetime | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 1|ord_num |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 4|payterms |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 3|qty |smallint | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 0|stor_id |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 10|sales |table | 5|title_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 3|city |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 4|state |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 2|stor_address |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 0|stor_id |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 1|stor_name |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 4|stores |table | 5|zip |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 5|titleauthor|table | 0|au_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 5|titleauthor|table | 2|au_ord |tinyint | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 5|titleauthor|table | 3|royaltyper |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 5|titleauthor|table | 1|title_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 5|advance |money | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 8|notes |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 4|price |money | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 3|pub_id |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 9|pubdate |datetime | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 6|royalty |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 1|title |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 0|title_id |varchar | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 2|type |char | 1| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 6|titles |table | 7|ytd_sales |int | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 2|au_lname |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 1|au_ord |tinyint | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 3|price |money | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 5|pub_id |char | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 0|title |varchar | 0| | 0 2021-01-29|15:16:31|d:\\sqlite32\\db\\pubs.db| 8|titleview |view | 4|ytd_sales |int | 0| | 0
(2) By Simon Slavin (slavin) on 2021-01-29 19:08:55 in reply to 1 [link]
Some of this information is not stored by SQLite (AtDate, column descriptions). Other parts of this information can be obtained using these commands <code>.databases PRAGMA schema.table_xinfo(table_name)</code> Do you have some specific reason to want the information in that format ?
(3) By anonymous on 2021-01-29 21:39:01 in reply to 2 [link]
>Do you have some specific reason to want the information in that format ? <b>ALL</b> the information is generated by SQLite3 table/view by table/view; there is no wizardry here. I am of a generation who wants to <i>see what exists</i> rather than of the generation who <i>find out when the need arises</i>, especially when chasing unexpected software behaviour or when inheriting an SQLite3 application The suggested dot command would be a time-saving convenience both for debugging and documentation, nothing more.
(4) By Simon Slavin (slavin) on 2021-01-30 02:08:48 in reply to 3 [link]
Pardon me. I didn't understand that the first table you included was a description of the second table, which was the subject of your comment. However, apart from having to iterate through the tables yourself, all that information is provided by the PRAGMA I listed. Perhaps I'm of the wrong generation to appreciate the advantage of having it listed all in one big table.
(5) By Mark Lawrence (mark) on 2021-01-31 13:23:21 in reply to 3 [link]
I find the table you have created valuable. Would you mind sharing the SQL you generate it with?
(6) By anonymous on 2021-01-31 15:08:07 in reply to 5
Alas, I have no SQL script, at least, no SQL script runnable from the SQLite3 CLI. I built the table using C# calling SQLite3.DLL directly. I can describe the process: <b>1. Get a list of tables:</b> ``` SELECT tbl_name FROM sqlite_master WHERE type IN ( 'table', 'view' ) AND tbl_name NOT LIKE 'sqlite%'; ``` with CHINOOK.DB, you will get something like this: ``` tbl_name -------------- albums artists customers employees genres invoices invoice_items media_types playlists playlist_track tracks ``` <b>2. Iterate the list with this SQL:</b> ``` select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums'); ``` ``` AtDate Database cid name type notnull dflt_value pk hidden ------------------- ---------- --- -------- ------------- ------- ---------- -- ------ 2021-01-31 14:53:49 chinook.db 0 AlbumId INTEGER 1 1 0 2021-01-31 14:53:49 chinook.db 1 Title NVARCHAR(160) 1 0 0 2021-01-31 14:53:49 chinook.db 2 ArtistId INTEGER 1 0 0 ``` You need to save the results in each iteration into a table. <b>EITHER</b> create a table with the first iteration with: ``` create table dbMap as select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums'); ``` and append to it with subsequent iterations. <b>OR</b> Create a table in each iteration ``` create table tblAlbums as select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums'); ``` If you choose the latter method, you need to collate the individual tables: ``` select * from tblAlbuns union all .... select * from tbltracks select * from tbl tracks ``` You can then redirect the output to a text file, more or less giving you what I [posted here](https://sqlite.org/forum/forumpost/d08a10f875?t=h). For the iteration, you'll need to use a programming language OR use clever recursive CTEs within the SQLiite3 CLI.