date:20180928

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

2018-09-28 Thread 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,
>>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

Re: [sqlite] A SQL statement reformatter

2018-09-28 Thread Simon Slavin

On 28 Sep 2018, at 3:49pm, Chris Brody  wrote:

> Someone should have make this tool 20-30 years ago!

They did.  There have been others available, I just posted a new one.







The last one has its essential parts (written in Python) in github.

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

Re: [sqlite] A SQL statement reformatter

2018-09-28 Thread Peter da Silva

Oh, nice!

On Fri, Sep 28, 2018 at 9:50 AM Chris Brody  wrote:

> I found https://github.com/mjibson/sqlfmt through their about page. No
> license though, just raised
> https://github.com/mjibson/sqlfmt/issues/33.
>
> GitHub fork-me ribbon would also be nice I think.
>
> Someone should have make this tool 20-30 years ago!
> On Fri, Sep 28, 2018 at 10:44 AM Peter da Silva  wrote:
> >
> > Pity they aren't making the code available. Not sure I want to send
> company
> > SQL to a random site.
> >
> > On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen 
> wrote:
> >
> > > Slick. That line width slider feature is something we don't get using
> Poor
> > > Man's Sql Formatter.
> > >
> > > Thanks!
> > >
> > > On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin 
> wrote:
> > >
> > > > For those times when you have to understand a poorly-formatted SQL
> > > > statement:
> > > >
> > > > 
> > > >
> > > > I seem to prefer 'full' mode.
> > > >
> > > > Simon.
> > > > ___
> > > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A SQL statement reformatter

2018-09-28 Thread Chris Brody

I found https://github.com/mjibson/sqlfmt through their about page. No
license though, just raised
https://github.com/mjibson/sqlfmt/issues/33.

GitHub fork-me ribbon would also be nice I think.

Someone should have make this tool 20-30 years ago!
On Fri, Sep 28, 2018 at 10:44 AM Peter da Silva  wrote:
>
> Pity they aren't making the code available. Not sure I want to send company
> SQL to a random site.
>
> On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen  wrote:
>
> > Slick. That line width slider feature is something we don't get using Poor
> > Man's Sql Formatter.
> >
> > Thanks!
> >
> > On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:
> >
> > > For those times when you have to understand a poorly-formatted SQL
> > > statement:
> > >
> > > 
> > >
> > > I seem to prefer 'full' mode.
> > >
> > > Simon.
> > > ___
> > > 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

Re: [sqlite] A SQL statement reformatter

2018-09-28 Thread Peter da Silva

Pity they aren't making the code available. Not sure I want to send company
SQL to a random site.

On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen  wrote:

> Slick. That line width slider feature is something we don't get using Poor
> Man's Sql Formatter.
>
> Thanks!
>
> On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:
>
> > For those times when you have to understand a poorly-formatted SQL
> > statement:
> >
> > 
> >
> > I seem to prefer 'full' mode.
> >
> > Simon.
> > ___
> > 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

2018-09-28 Thread 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

2018-09-28 Thread 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

2018-09-28 Thread 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

2018-09-28 Thread 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 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,
>>Collation

Re: [sqlite] A SQL statement reformatter

2018-09-28 Thread Don V Nielsen

Slick. That line width slider feature is something we don't get using Poor
Man's Sql Formatter.

Thanks!

On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:

> For those times when you have to understand a poorly-formatted SQL
> statement:
>
> 
>
> I seem to prefer 'full' mode.
>
> Simon.
> ___
> 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

2018-09-28 Thread 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 
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 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

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

2018-09-28 Thread 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 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

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

2018-09-28 Thread 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

Re: [sqlite] A SQL statement reformatter

Re: [sqlite] A SQL statement reformatter

Re: [sqlite] A SQL statement reformatter

Re: [sqlite] A SQL statement reformatter

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

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

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

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

Re: [sqlite] A SQL statement reformatter

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

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

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

13 matches

Site Navigation

Mail list logo

Footer information