Solved

SQLTables

Posted on 2000-04-21
5
197 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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