Thanks Keith.. but I am getting an error while joining pragma table info.
On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf <kmedc...@dessus.com> wrote:
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
