What version of SQLite are you using?
--- 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: Friday, 28 September, 2018 00:25 >To: SQLite mailing list >Subject: Re: [sqlite] How to retrieve table names for the given >string > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
