Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQLTables

Posted on 2000-04-21
5
Medium Priority
?
207 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
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 …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
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…

618 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