Insert the following schema views:
-- 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" attachmented 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
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,
Type collate nocase,
Affinity collate nocase,
isNotNull,
DefaultValue,
isPrimaryKey
from (
select ObjectType,
ObjectName,
cid as ColumnID,
name as ColumnName,
type as Type,
--- Affinity Rules from https://www.sqlite.org/datatype3.html
Section 3.1
case when trim(type) = '' then 'Blob'
when instr(UPPER(type), 'INT') > 0 then 'Integer'
when instr(UPPER(type), 'CLOB') > 0 then 'Text'
when instr(UPPER(type), 'CHAR') > 0 then 'Text'
when instr(UPPER(type), 'TEXT') > 0 then 'Text'
when instr(UPPER(type), 'BLOB') > 0 then 'Blob'
when instr(UPPER(type), 'REAL') > 0 then 'Real'
when instr(UPPER(type), 'FLOA') > 0 then 'Real'
when instr(UPPER(type), 'DOUB') > 0 then 'Real'
else 'Numeric'
end 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,
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)
);
then
select ObjectName as TableName
from SysColumns
where ColumnName == ?
and ObjectType = 'table';
---
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 Revathi Narayanan
>Sent: Thursday, 27 September, 2018 07:44
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] How to retrieve table names for the given string
>
>Hi,
>
>I have one requirement like I want to display all the table names for
>the
>given column name.
>
>Ex: If the table T1 and T2 has column names like C1 then it should
>display
>both the table names T1 and T2.
>
>I tried to execute the query using sqlitemaster. But it's displaying
>only
>table names not column names.
>
>Kindly do the needful.
>
>
>Thanks
>_______________________________________________
>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
