Re: [sqlite] How to retrieve table names for the given string

[Keith Medcalf]

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 
>wrote:
>
>>
>> Insert the following schema views:
>>
>>
>> -- Catalog Views using sqlite_master for SysObjects (Object Names)
>> -- and the various pragma_(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,
>>IndexNamecollate 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,
>>IndexNamecollate nocase,
>>In

Re: [sqlite] How to retrieve table names for the given string

[Igor Tandetnik]

On 9/28/2018 9:51 AM, Thomas Kurz wrote:

No, it's PRAGMA table_info (...);


It has to be pragma_table_info if you want to use it as part of a SELECT 
statement. See https://www.sqlite.org/pragma.html#pragfunc
--
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[Thomas Kurz]

No, it's PRAGMA table_info (...);


- Original Message - 
From: Igor Tandetnik 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, September 28, 2018, 15:35:30
Subject: [sqlite] How to retrieve table names for the given string

On 9/28/2018 2:16 AM, Revathi Narayanan wrote:
> Thanks Richard. But I am getting an error like near ( syntax error.

> Pragma table_info(a.name)

It's pragma_table_info , in one word; two underscores, no spaces.
-- 
Igor Tandetnik


___
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

Re: [sqlite] How to retrieve table names for the given string

[Igor Tandetnik]

On 9/28/2018 2:16 AM, Revathi Narayanan wrote:

Thanks Richard. But I am getting an error like near ( syntax error.

Pragma table_info(a.name)


It's pragma_table_info , in one word; two underscores, no spaces.
--
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[David Raymond]

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:sqlite-users-boun...@mailinglists.sqlite.org] 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 
wrote:

> Thanks Keith.. but I am getting an error while joining pragma table info.
>
> On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf  wrote:
>
>>
>> Insert the following schema views:
>>
>>
>> -- Catalog Views using sqlite_master for SysObjects (Object Names)
>> -- and the various pragma_(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,
>>IndexNamecollate nocase,
>>IndexID,
>>isUniqueIndex,
>>IndexOrigin  collate no

Re: [sqlite] How to retrieve table names for the given string

[Revathi Narayanan]

xName,
>>            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

Re: [sqlite] How to retrieve table names for the given string

[Revathi Narayanan]

Thanks Keith.. but I am getting an error while joining pragma table info.

On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf  wrote:

>
> Insert the following schema views:
>
>
> -- Catalog Views using sqlite_master for SysObjects (Object Names)
> -- and the various pragma_(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,
>IndexNamecollate 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,
>IndexNamecollate nocase,
>IndexColumnSequence,
>ColumnID,
>ColumnName   collate nocase,
>isDescendingOrder,
>Collationcollate nocase,
>isPartOfKey
>   from (
> select ObjectType,
>ObjectName,
>IndexName,
>seqnoas 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 th

Re: [sqlite] How to retrieve table names for the given string

[Revathi Narayanan]

Thanks Richard. But I am getting an error like near ( syntax error.

Pragma table_info(a.name)

On Fri, Sep 28, 2018, 6:21 AM Richard Hipp  wrote:

> On 9/27/18, Revathi Narayanan  wrote:
> > 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.
>
> Let the column name be in the variable $c1
>
>SELECT a.name
>FROM sqlite_master AS a
>JOIN pragma_table_info(a.name) AS b
>WHERE a.type='table'
>AND b.name=$c1;
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] How to retrieve table names for the given string

[Keith Medcalf]

Insert the following schema views:


-- Catalog Views using sqlite_master for SysObjects (Object Names)
-- and the various pragma_(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,
   IndexNamecollate 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,
   IndexNamecollate nocase,
   IndexColumnSequence,
   ColumnID,
   ColumnName   collate nocase,
   isDescendingOrder,
   Collationcollate nocase,
   isPartOfKey
  from (
select ObjectType,
   ObjectName,
   IndexName,
   seqnoas 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 d

Re: [sqlite] How to retrieve table names for the given string

[Richard Hipp]

On 9/27/18, Revathi Narayanan  wrote:
> 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.

Let the column name be in the variable $c1

   SELECT a.name
   FROM sqlite_master AS a
   JOIN pragma_table_info(a.name) AS b
   WHERE a.type='table'
   AND b.name=$c1;



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[Igor Tandetnik]

On 9/27/2018 9:43 AM, Revathi Narayanan wrote:

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.


With sufficiently recent SQLite version, you can select from 
pragma_table_info('tablename') and get the same resultset as PRAGMA 
table_info(tablename): https://www.sqlite.org/pragma.html#pragfunc . These 
functions can participate in joins: the table name doesn't have to be a literal.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to retrieve table names for the given string

[Simon Slavin]

On 27 Sep 2018, at 2:43pm, Revathi Narayanan  wrote:

> I tried to execute the query using sqlitemaster. But it's displaying only
> table names not column names.

sqlite_master does not have column name columns.  They're just mentioned in the 
CREATE statement.

You might want to combine it with



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to retrieve table names for the given string

[Revathi Narayanan]

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