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

>>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.

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 -----

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

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

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

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   _____  

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   _____  

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.

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