?
Solved

SQLTables

Posted on 2000-04-21
5
Medium Priority
?
206 Views
Last Modified: 2013-12-24
Hi, I am new to Cold Fusion.  In C/C++ you could call the ODBC Catalog function SQLTables(...) and return a listing of all the tables in the specified database.  I would like to do this in Cold Fusion but, I haven't found a way to do it.  Of course in SQL Server you simply have to call sp_tables and it returns the tables but, I need a solution like SQLTables that is more generic than that.  i.e. can be used with more than one kind of database.  An example would be great!

Thanks,
Nathan Perrizo
0
Comment
Question by:perrizo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 5

Accepted Solution

by:
nathans earned 400 total points
ID: 2739504
Check out my www.nsnd.com

I publish ColdFusion Tips Plus - Some JavaScript...

I have been working on what your wanting... I have Oracle, MS SQL, and Access.

There is NO tool that does this in ColdFusion for you.


========================================================
III. ColdFusion Code:  Dynamically get the Table Names
     from a ODBC in Oracle, MS SQL, or MS Access

I am working on a tool that will allow you to have crud screens
as soon as you install it.  For every table that you have.  
This is a great admin tool.

========================================================
<cfset datasource="messageboard">

<!---
This is the MS SQL way to get the tables and columns
 --->
<!---
<cfquery name="GetData" datasource="#datasource#">
SELECT name "TableName"
FROM sysobjects
WHERE type = 'U'
</cfquery>
 --->
<!---
This is the Oracle way to get a list of all of the
tables in a datasource.
 --->
<!---
<cfquery name="gettables" datasource="PAMS">
Select TABLE_NAME
from ALL_TABLES
Order by TABLE_NAME
</cfquery>
 --->

<!---
This is the MS Access way to get a list of all of the
tables in a datasource.  In MS Access you have to first
make all of the hidden and system fields available.
Then in security you give admin right to your system
tables.


 --->
<cfquery name="GetData" datasource="#datasource#">
SELECT *
FROM MSysObjects
WHERE TYPE=1 and
left(MSysObjects.name,4) <> 'MSys'
</cfquery>


<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css"
href="/test/includes/style.css">
</head>
<body>

<table border="1" bordercolor="000000"
cellpadding="0" cellspacing="0">
<cfoutput query="GetData">
<tr>
      <td><font size="-1">#Name#</font></td>
</tr>
</cfoutput>
</table>

</body>
</html>
========================================================
0
 
LVL 1

Expert Comment

by:cfmrulez
ID: 2788717
Hi!

In Oracle, if you wanna the table corresponding to a single user user this evolution of the nathans' one.

select
owner, table_name
from
all_tables
where
owner = 'YourUser';

Only a comment,
cfmrulez!
0
 
LVL 5

Expert Comment

by:nathans
ID: 2790285
Can you please either grade this answer or shoot me....

I wonder if I should have ever answered this one.
0
 
LVL 1

Author Comment

by:perrizo
ID: 2790495
Sorry for the delay.  Your answer was good... while looking more into it I found another way that distracted me from grading... I was trying to use the CFOBJECT tag to use the ADODB object to perform the same tasks... you should check it out it's pretty cool.
0
 
LVL 5

Expert Comment

by:nathans
ID: 2790508

Where have you been getting the information about it?


Did you check out my site?

Nathan
www.nsnd.com
ColdFusion Tips Plus

I have learned to use FuseBox Methodology to program it is cool.
0

Featured Post

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question