0
687

Written by

Victor Li
Follow
Question Victor Li · Nov 12, 2017

How to batch export/save SQL tables' detail

#Caché

Hello Caché Guru,

I'm new to Cache system. I'm trying to do something like saving meta data in Relational DB:

I could use "management portal" -> System Explorer->SQL to view individual table's catalog details or print out the column definition. However I need to gather those information for several hundreds of tables in the DB. Is there a way to do it using command line in kind of batching mode?

Any hint and tip is very much appreciated!

Thanks in advance!

Victor

Discussion (3)0
Log in or sign up to continue

Comments

Pravin Barton · Nov 13, 2017

Hi Victor,

You can use the class queries  of %Library.SQLCatalog to find catalog details for tables.

The SQLTables query gives you a list of tables:

select * from %Library.SQLCatalog_SQLTables()

And the SQLFields query will give you a list of fields for a given table:

select * from %Library.SQLCatalog_SQLFields('sample table name')

You can run these queries in the command line using dynamic sql, for example:

set sql = ##class(%SQL.Statement).%New()
write sql.%PrepareClassQuery("%Library.SQLCatalog","SQLTables")
set rs = sql.%Execute()
do rs.%Display()
0
Victor Li  Nov 15, 2017 to Pravin Barton

Hi  Pravin,

Thank you for your response. I tried your query in our system, but it gave me the following error:

Table "%Library.SQLCatalog_SQLTables" not found.

I tried namespace "TRAK" and "%SYS", none of those worked.

When I opened the %Library schema on the left panel, there is only one table with "SQL" in the name:  %Library.sys_SQLConnection

I can't paste the screenshot here, the link I used in browser for the above test is:

http://XX.XX.XX.XX:YYYY/csp/sys/exp/%25CSP.UI.Portal.SQL.Home.zen?$NAME…

I'm using superuser account. Wonder if it's related to permission?

Thanks a lot!

Victor

0