How to get all table names on ODBC connection

Post Reply
sk1080in
Posts: 23
Joined: Mon Mar 23, 2015 12:08 am
OLAP Product: TM1
Version: Planning Analytics
Excel Version: 2007

How to get all table names on ODBC connection

Post by sk1080in »

Hi,

Is there a way to get all table names in Turbo Integrator Process by selecting ODBC as datasource type ? I have few queries already working, however need to explore more tables but donot know the name of other tables.

Thanks you
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How to get all table names on ODBC connection

Post by Wim Gielis »

Not really. You would be better off logging on to the relational database to which the ODBC is connecting.
Maybe (I'm not an expert of these systems) there might be a table itself, containing the names of the tables.
But then again, even if that's possible, just log on as I mentioned above.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
sk1080in
Posts: 23
Joined: Mon Mar 23, 2015 12:08 am
OLAP Product: TM1
Version: Planning Analytics
Excel Version: 2007

Re: How to get all table names on ODBC connection

Post by sk1080in »

Thank you Wim
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: How to get all table names on ODBC connection

Post by gtonkin »

I would also use some client tools to connect to the database but if you only have TI then use it.

I use the following:
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES

Should return the tables then you can use:
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name='<tablename>'
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to get all table names on ODBC connection

Post by lotsaram »

gtonkin wrote: Mon Dec 17, 2018 5:03 am I would also use some client tools to connect to the database but if you only have TI then use it.
"Only having TI" is a very rare if ever occurrence. There are numerous lightweight SQL IDEs which don't require install. And in those very locked down environments such as large banks I can't imagine there wouldn't be an approved SQL client either already installed or able to be ordered without much fuss.

The TI query window is hardly a query building environment! Use each tool for what it's good for and develop the query in an appropriate tool and copy/paste into TI. Otherwise you're writing the query blind which is just asking for problems.

The query to run to return a list of table names is going to depend on the type of RDBMS. The query from gtonkin will work for MS SQLServer or Postgres
but for Oracle it would be SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
or for MySQL the much simpler SHOW TABLES

... but a list of tables doesn't get you very far. If you are going to write queries then you need to know the fields and especially the primary keys & foreign keys. For this you really need a tool that is built for the job.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
sk1080in
Posts: 23
Joined: Mon Mar 23, 2015 12:08 am
OLAP Product: TM1
Version: Planning Analytics
Excel Version: 2007

Re: How to get all table names on ODBC connection

Post by sk1080in »

gtonkin wrote: Mon Dec 17, 2018 5:03 am I would also use some client tools to connect to the database but if you only have TI then use it.

I use the following:
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES

Should return the tables then you can use:
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name='<tablename>'
Thank you Gtonkin...It really worked !!!
sk1080in
Posts: 23
Joined: Mon Mar 23, 2015 12:08 am
OLAP Product: TM1
Version: Planning Analytics
Excel Version: 2007

Re: How to get all table names on ODBC connection

Post by sk1080in »

lotsaram wrote: Mon Dec 17, 2018 9:54 am
gtonkin wrote: Mon Dec 17, 2018 5:03 am I would also use some client tools to connect to the database but if you only have TI then use it.
"Only having TI" is a very rare if ever occurrence. There are numerous lightweight SQL IDEs which don't require install. And in those very locked down environments such as large banks I can't imagine there wouldn't be an approved SQL client either already installed or able to be ordered without much fuss.

The TI query window is hardly a query building environment! Use each tool for what it's good for and develop the query in an appropriate tool and copy/paste into TI. Otherwise you're writing the query blind which is just asking for problems.

The query to run to return a list of table names is going to depend on the type of RDBMS. The query from gtonkin will work for MS SQLServer or Postgres
but for Oracle it would be SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
or for MySQL the much simpler SHOW TABLES

... but a list of tables doesn't get you very far. If you are going to write queries then you need to know the fields and especially the primary keys & foreign keys. For this you really need a tool that is built for the job.
Thank you lotsaram, Understand your point completely. For me it is a SQL environment which I have access through ODBC only, so no one other knows how to access the tables directly. I was looking for some specific columns and found them in one of the tables. Agreed that writing the query in TI would not be a complete picture.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How to get all table names on ODBC connection

Post by Wim Gielis »

lotsaram wrote: Mon Dec 17, 2018 9:54 am"Only having TI" is a very rare if ever occurrence.
Rare case, but apparently, it exists :shock:
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply