-- 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-
>boun...@mailinglists.sqlite.org] On Behalf Of mike otwell
>Sent: Friday, 2 March, 2018 07:04
>To: sqlite-users@mailinglists.sqlite.org
>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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
