You can translate the question and the replies:

Getting Source Tables and Columns of a Datasource through Query

Hi Denodo team, Are there any queries to get all the tables in the datasource and also columns in a datasource for a specified table. I have seen that GET_SOURCE_TABLE() procedure is giving table but it takes view name as a parameter,but I don't want to give view as parameter,but only datasource name as parameter. Any help is highly appreciated. Thanks, Kranthi kiran.
user
13-10-2020 04:41:34 -0400
code

5 Answers

Hi I would use a join operation between the stored procedures GETSOURCECOLUMNS(), GETSOURCETABLE() and GETVIEWS() to pass all view names of a database and achieved the desired information. You could take a look at a similar question asked [here](https://community.denodo.com/answers/question/details?questionId=9060g000000kAyrAAE&title=Meta+data+information+-+SP%C2%B4s+get_source_table%28%29+and+GET_SOURCE_COLUMNS%28%29). You can also take a look at [Predefined stored procedures](https://community.denodo.com/docs/html/browse/latest/vdp/vql/stored_procedures/predefined_stored_procedures/predefined_stored_procedures) Virtual DataPort guide for more details on these and similar procedures. You could also use [GET_JDBC_SOURCE_TABLES](https://community.denodo.com/docs/html/browse/latest/vdp/vql/stored_procedures/predefined_stored_procedures/get_jdbc_datasource_tables) stored procedure to get the tables in the underlying data source. It accepts data source name as an input parameter. You can use [GET_VIEW_COLUMNS](https://community.denodo.com/docs/html/browse/latest/vdp/vql/stored_procedures/predefined_stored_procedures/get_view_columns) stored procedure to get information on all the columns of a view. The view accepts database name (denodo database) and view name as parameter but both are optional and can be set to null. If they are set to null column details of all the views are returned. Note that table and column details of JDBC data sources only are returned.* Hope this helps!
Denodo Team
14-10-2020 06:23:36 -0400
code
Hi Denodo Team, In the above answer you have mentioned a **Note** at the end of answer saying **table and column details of JDBC data sources only are returned,** then how to the get the table and column details of non JDBC data sources like SAPBWBAPI or any multidimensional data sources ? Any help will be highly appreciated. Thanks, Kranthi kiran.
user
07-06-2021 14:04:00 -0400
Hi, Using the [Predefined Stored Procedures](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/stored_procedures/predefined_stored_procedures/predefined_stored_procedures), it is only possible to retrieve the underlying JDBC data source’s table/column details. You could also refer to a similar community [“Getting the Source Tables and columns for non-JDBC datasources”](https://community.denodo.com/answers/question/details?questionId=9064u000000L8PqAAK&title=Getting+the+Source+Tables+and+columns+for+non-JDBC+datasources) to get more information. Hope this helps!
Denodo Team
05-11-2021 05:06:18 -0400
code
Hi, Its still not clear here about how to get columns. GET_JDBC_SOURCE_TABLES can be used to get the tables from JDBC data sources. But how to get the columns of those tables. GET_SOURCE_COLUMNS also requires base view to get information. IS there a way to get the column information before creating a base view just by using the datasource. Regards, Sanal
user
16-11-2021 05:20:22 -0500
Hi, For your use case of getting the column information before creating the base views from the data source, I would do the following steps to achieve it using the Graphical User Interface, * Open the JDBC data source by double-clicking it in the **Server Explorer**. * Click **Create base view**, the schemas of the database will be displayed. * Click on any schema, to inspect its tables. * The tables can be further expanded to view the column details. Alternatively, I could also **create base view** on top of the JDBC data source using the “[Create from query](https://community.denodo.com/kb/en/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option)” option in order to get the source column details in the table format, * For example, to get the column details of the Microsoft SQL Server tables, I would use a similar SQL query for creating base view, `SELECT TAB.NAME AS TABLE_NAME, TAB.OBJECT_ID AS OBJECT_ID, COL.NAME AS COLUMN_NAME, TYP.NAME AS DATA_TYPE_NAME, TYP.MAX_LENGTH AS MAX_LENGTH FROM SYS.COLUMNS COL INNER JOIN SYS.TABLES TAB ON COL.OBJECT_ID = TAB.OBJECT_ID INNER JOIN SYS.TYPES TYP ON TYP.USER_TYPE_ID = COL.USER_TYPE_ID` Hope this helps!
Denodo Team
17-11-2021 01:37:07 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here