Question:
How can I return all the tables existing in an Access, Oracle, or SQL database?
Solution:
To do so, follow the procedures below according to the database type.
Access
To return the tables that are inside an Access file:
1. Open the Access file.
2. Go to where the user-generated tables are, right-click it and select Browsing Options.
3. On Browsing Options, locate the display options, check Show system objects, and click OK.
4. Still on Access, click File tab.
5. On this tab, click Users and permissions, and then Permissions for users and groups.
6. A window will be opened for user settings; on this window, on Permissions tab, select the Administrator use and check Table in object types.
7. On the tables list (upper right corner), locate and select MSysObjects table.
8. After selecting MSysObjects table, enable all Admin permissions for this table and click OK.
9. On E3, create a query where you wish to use the information of the names of the tables existing in the database (for example, on a screen).
10. Right-click the query and select Configure.
11. On the configuration window, access SQL tab, check Enable SQL customization, and type the following code:
FROM MSysObjects
WHERE (((Left(Name,1))“~”) AND ((Left(Name,4))“MSys”) AND ((MSysObjects.Type) In (1,4,6)))
Order by MSysObjects.Name
12. To check whether the query is right, access Visualize tab and click the red exclamation point to return the results of your query.
SQL
To return the tables that are inside an SQL database:
1. Create a query where you wish to use the information of the names of the tables existing in the database (for example, on a screen).
2. Right-click the query and select Configure.
3. On the configuration window, access SQL tab, check Enable SQL customization, and type the following code:
FROM SysObjects
WHERE Xtype=’U’
4. To check whether the query is right, access Visualize tab and click the red exclamation point to return the results of your query.
Oracle
To return the tables that are inside an Oracle database:
1. Create a query where you wish to use the information of the names of the tables existing in the database (for example, on a screen).
2. Right-click the query and select Configure.
3. On the configuration window, access SQL tab, check Enable SQL customization, and type the following code:
FROM ALL_TABLES
WHERE (NOT TABLE_NAME LIKE ‘%$%’) AND (NOT TABLE_NAME LIKE ‘%LOGMNR%’) AND (OWNER = ‘‘)
4. The user variable must be passed as query parameter. It stands for the user who recorded the data (a user set up in the database); Oracle’s default user is SYSTEM. Make sure you’ve typed it in upper case; in case you need it, use UCase function.
5. To check whether the query is right, access Visualize tab and click the red exclamation point to return the results of your query; pass the user name when required.
