Solved

SQLTables

Posted on 2000-04-21
5
184 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
  • 3
5 Comments
 
LVL 5

Accepted Solution

by:
nathans earned 100 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now