Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Get Database Tables, etc
Author: Greg Luce
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165590
What database? MSSQL I use sp_tables.
I know I have seen this on the list before but I can't find the exact code.
Does anyone have the code for querying a database and returning a list of
tables and/or column info?
TIA,
Robbie
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165569
Robert Shaw wrote:
> This is DB2 on MX...
DB2 doesn't use the information schema, but the syscat schema:
http://www-306.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2s003.htm#ToC_557
Jochem
Author: Robert Shaw
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165540
This is DB2 on MX...
>>Does anyone have the code for querying a database and returning a list of
tables and/or column info?
Depending on the database you're using, there is some code possible, but not
for all databases.
For any kind of ODBC database, CFX_ODBCInfon is the sure way.
See the address below.
Author: Shawn Grover
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165539
In MS SQL Server (2000), you can use the system stored procedure sp_tables.
Issuing the command:
exec sp_tables
will list all the tables in the current database, with a column for table type
(System or Table). More details are in the online help for T-SQL.
There are similar stored procs for getting the primary keys, foriegn keys, and
lists of databases. I also know database systems other than MS SQL have similar
functions for getting the meta data of a database.
HTH
Shawn
I know I have seen this on the list before but I can't find the exact code.
Does anyone have the code for querying a database and returning a list of
tables and/or column info?
TIA,
Robbie
_____
Author: Semrau Steven Ctr SAF/IE
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165532
I'm using Oracle 9i:
SELECT object_name
FROM user_objects
WHERE object_type in ('TABLE', 'VIEW')
This will get you the table and view names.
SELECT column_name
FROM user_tab_columns
WHERE table_name = 'your_table_name_here'
This will get you the columns for that particular table.
SELECT DISTINCT table_name, column_name
FROM user_tab_columns
ORDER BY 1,2
This will get you the table names with their associated column names.
HTH
I know I have seen this on the list before but I can't find the exact code.
Does anyone have the code for querying a database and returning a list of
tables and/or column info?
TIA,
Robbie
_____
Author: mavinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165527
you could use
<cfquery name="fetchStuff" datasource="myDsn">
select * from myTable
</cfquery>
<cfdump var="#fetchStuff#">
-mike
"Robert Shaw" <robertshawsjaws@hotmail.com>
06/04/2004 11:47 AM
Please respond to cf-talk
To: CF-Talk <cf-talk@houseoffusion.com>
cc:
Subject: Get Database Tables, etc
I know I have seen this on the list before but I can't find the exact
code.
Does anyone have the code for querying a database and returning a list of
tables and/or column info?
TIA,
Robbie
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165524
Robert Shaw wrote:
> I know I have seen this on the list before but I can't find the exact code.
> Does anyone have the code for querying a database and returning a list of
> tables and/or column info?
If your database is compliant with the SQL spec you can get all
off that from the information schema:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
Jochem
Author: Mark Drew
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165523
Depends on the DB, I know that you can do "show tables" with mySQL
for MS SQL you can do a query to the sysobjects table as follows:
SELECT *
FROM sysobjects
WHERE (xtype = 'U')
HTH
Mark Drew
----- Excess quoted text cut - see Original Post for more -----
Author: Claude Schneegans
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165521
>>Does anyone have the code for querying a database and returning a list of
tables and/or column info?
Depending on the database you're using, there is some code possible, but not for
all databases.
For any kind of ODBC database, CFX_ODBCInfon is the sure way.
See the address below.
--
_______________________________________
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: piegeacon@internetique.com)
Thanks.
Author: Robert Shaw
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32994#165519
I know I have seen this on the list before but I can't find the exact code.
Does anyone have the code for querying a database and returning a list of
tables and/or column info?
TIA,
Robbie
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
