SQLite Forum

Suggestion a new dot command (.map)
Login

Suggestion a new dot command (.map)

(1) By anonymous on 2021-01-29 15:38:37

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 [link]

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.