List of tables in SQL

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
3 posts Page 1 of 1

monahanks

Posts: 41
Joined: Wed Mar 30, 2011 10:19 am

Post by monahanks » Fri Mar 10, 2017 9:51 am
Good morning,
I am primarily an IBMi guy and SQL is pretty new to me, so bear with me on this question.
is there a way in GoAnywhere to get a list of tables in a SQL database on a SQL server? For example, using FTP I can run a LS command to get a list of files from an IBMi. I want to be able to do something similar using a database server, build a FileList in GA, and use the table names as a variable in a SQL Select command such as
Select * from ${tablename}

If this is not available, would I be able to have a GA project that looped through a text (or csv) file with table names and used each name as a variable in the select statement above?

TIA,
Kevin

monahanks

Posts: 41
Joined: Wed Mar 30, 2011 10:19 am

Post by monahanks » Fri Mar 10, 2017 1:36 pm
I found a solution using a search

SELECT name
FROM sysobjects
WHERE xtype='U'
and name like 'PROMO%'
ORDER BY name;

this gives me a list of tables in a database whose name begins with PROMO. now I need to read this list of tables and copy/move each table to another server or archive it.

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Sat Mar 11, 2017 10:46 am
Kevin,

You can also address "SysTables" in the SysIBM library. It can give you quite a bit to query from to get those lists. There are other indexes and tables in that same library that you can use as well.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
3 posts Page 1 of 1