How to retrieve table names for the given string

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Simon Slavin-3
On 27 Sep 2018, at 2:43pm, Revathi Narayanan <[hidden email]> 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

<https://www.sqlite.org/pragma.html#pragma_table_info>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Igor Tandetnik-2
In reply to this post by Revathi Narayanan
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Richard Hipp-3
In reply to this post by Revathi Narayanan
On 9/27/18, Revathi Narayanan <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Keith Medcalf
In reply to this post by Revathi Narayanan

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-
>[hidden email]] On Behalf Of Revathi Narayanan
>Sent: Thursday, 27 September, 2018 07:44
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Revathi Narayanan
In reply to this post by Richard Hipp-3
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 <[hidden email]> wrote:

> On 9/27/18, Revathi Narayanan <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Revathi Narayanan
In reply to this post by Keith Medcalf
Thanks Keith.. but I am getting an error while joining pragma table info.

On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf <[hidden email]> 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-
> >[hidden email]] On Behalf Of Revathi Narayanan
> >Sent: Thursday, 27 September, 2018 07:44
> >To: [hidden email]
> >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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Revathi Narayanan
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 <[hidden email]>
wrote:

> Thanks Keith.. but I am getting an error while joining pragma table info.
>
> On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf <[hidden email]> 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-
>> >[hidden email]] On Behalf Of Revathi Narayanan
>> >Sent: Thursday, 27 September, 2018 07:44
>> >To: [hidden email]
>> >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
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] 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 <[hidden email]>
wrote:

> Thanks Keith.. but I am getting an error while joining pragma table info.
>
> On Fri, Sep 28, 2018, 6:30 AM Keith Medcalf <[hidden email]> 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-
>> >[hidden email]] On Behalf Of Revathi Narayanan
>> >Sent: Thursday, 27 September, 2018 07:44
>> >To: [hidden email]
>> >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
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Igor Tandetnik-2
In reply to this post by Revathi Narayanan
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

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


----- Original Message -----
From: Igor Tandetnik <[hidden email]>
To: [hidden email] <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to retrieve table names for the given string

Keith Medcalf
In reply to this post by Revathi Narayanan

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-
>[hidden email]] 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 <[hidden email]>
>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-
>> >[hidden email]] On Behalf Of Revathi Narayanan
>> >Sent: Thursday, 27 September, 2018 07:44
>> >To: [hidden email]
>> >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
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users