pragma table_info(tbl)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pragma table_info(tbl)

mike otwell
I have a table named person that contains 13 columns.
pragma table_info(person) returns 13 rows of cid.
I assume this is the primary key... do I need to add something to get the
column name along with cid?

--
No trees were killed in the sending of this message. However, a large
number of electrons were terribly inconvenienced.
mike(the uber geek)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma table_info(tbl)

Keith Medcalf

-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_<infotype>(ObjectName) tables to retrieve schema data
-- all TEXT columns in views have "collate nocase" attached to the output
-- columns to ensure that where conditions on retrievals are not case sensitive
-- Column Names in views defined so as to not conflict with keywords to ensure
-- quoting when using views is not required
-- only works in database "main" at the moment

drop view if exists SysIndexColumns;
drop view if exists SysIndexes;
drop view if exists SysColumns;
drop view if exists SysObjects;

create view if not exists SysObjects
as
select ObjectType collate nocase,
       ObjectName collate nocase
  from (
        select type as ObjectType,
               name as ObjectName
          from sqlite_master
         where type in ('table', 'view', 'index')
       );

create view if not exists SysColumns
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       ColumnID collate nocase,
       ColumnName collate nocase,
       Affinity collate nocase,
       IsNotNull,
       DefaultValue,
       IsPrimaryKey
from (
        select ObjectType,
               ObjectName,
               cid        as ColumnID,
               name       as ColumnName,
               type       as Affinity,
               "notnull"  as IsNotNull,
               dflt_value as DefaultValue,
               pk         as IsPrimaryKey
          from SysObjects
          join pragma_table_info(ObjectName)
     );

create view if not exists SysIndexes
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexID,
       IsUniqueIndex collate nocase,
       IndexOrigin collate nocase,
       IsPartialIndex
  from (
        select ObjectType,
               ObjectName,
               name     as IndexName,
               seq      as IndexID,
               "unique" as IsUniqueIndex,
               origin   as IndexOrigin,
               partial  as IsPartialIndex
          from SysObjects
          join pragma_index_list(ObjectName)
       );

create view if not exists SysIndexColumns
as
select ObjectType collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexColumnSequence,
       ColumnID,
       ColumnName collate nocase,
       IsDescendingOrder,
       Collation collate nocase,
       IsPartOfKey
  from (
        select ObjectType,
               ObjectName,
               IndexName,
               seqno  as IndexColumnSequence,
               cid    as ColumnID,
               name   as ColumnName,
               "desc" as IsDescendingOrder,
               coll   as Collation,
               key    as IsPartOfKey
          from SysIndexes
          join pragma_index_xinfo(IndexName)
       );



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of mike otwell
>Sent: Friday, 2 March, 2018 07:04
>To: [hidden email]
>Subject: [sqlite] pragma table_info(tbl)
>
>I have a table named person that contains 13 columns.
>pragma table_info(person) returns 13 rows of cid.
>I assume this is the primary key... do I need to add something to get
>the
>column name along with cid?
>
>--
>No trees were killed in the sending of this message. However, a large
>number of electrons were terribly inconvenienced.
>mike(the uber geek)
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma table_info(tbl)

David Raymond
In reply to this post by mike otwell
cid is the column number, to get the primary key look in the pk field and the primary key will have numbers 1, 2 etc. for each field in the primary key.

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (a int default -1, b text not null, c real not null, d, e blob, primary key (c, b));

sqlite> .nullvalue NuLL

sqlite> pragma table_info(foo);
cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           a           int         0           -1          0
1           b           text        1           NuLL        2
2           c           real        1           NuLL        1
3           d                       0           NuLL        0
4           e           blob        0           NuLL        0

sqlite> pragma table_info(pragma_table_info);
cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           cid                     0                       0
1           name                    0                       0
2           type                    0                       0
3           notnull                 0                       0
4           dflt_value              0                       0
5           pk                      0                       0



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of mike otwell
Sent: Friday, March 02, 2018 9:04 AM
To: [hidden email]
Subject: [sqlite] pragma table_info(tbl)

I have a table named person that contains 13 columns.
pragma table_info(person) returns 13 rows of cid.
I assume this is the primary key... do I need to add something to get the
column name along with cid?

--
No trees were killed in the sending of this message. However, a large
number of electrons were terribly inconvenienced.
mike(the uber geek)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users