SQLTables

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
LVL 1
perrizoAsked:
Who is Participating?
 
Nathan Stanford SrConnect With a Mentor Senior ProgrammerCommented:
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
 
cfmrulezCommented:
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
 
Nathan Stanford SrSenior ProgrammerCommented:
Can you please either grade this answer or shoot me....

I wonder if I should have ever answered this one.
0
 
perrizoAuthor Commented:
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
 
Nathan Stanford SrSenior ProgrammerCommented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.