What if I ask you for tables with a column named "integer"? Or if there are
comments in the table sql which might have the names of other tables?
Give this a whirl and let me know if it works ok.
(Also a good chance to use the statement reformatter from a recent post)
with recursive foo (tableName, fieldName, fieldNum) as (
select name, null, null from sqlite_master where type = 'table'
union all
select foo.tableName, bar.name, bar.cid
from foo inner join pragma_table_info(foo.tableName) as bar
on true
where foo.fieldName is null
)
select * from foo where fieldName is not null order by tableName, fieldNum;
"Should" give a list of all tables and their fields. Then if you just want what
has a specific field name you can select from there.
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Revathi Narayanan
Sent: Friday, September 28, 2018 2:40 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to retrieve table names for the given string
I got the output by executing the below query,
select * from sqlite_master where sql like '%column name%
On Fri, Sep 28, 2018, 11:55 AM Revathi Narayanan <[email protected]>
wrote:
> Thanks Keith.. but I am getting an error while joining pragma table info.
>
> On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf <[email protected]> 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-
>> >[email protected]] On Behalf Of Revathi Narayanan
>> >Sent: Thursday, 27 September, 2018 07:44
>> >To: [email protected]
>> >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
>> >[email protected]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
