I'd not responded because the original request was for MS Access which I
haven't done any real work with in a while... In MS SQL Server, I usually
don't go to the extent of using the system tables for this, I just use the
information_schema views...
SELECT t.table_name, c.column_name,
c.data_type, c.character_maximum_length
FROM information_schema.tables t
INNER JOIN information_schema.columns c
on ( c.table_name = t.table_name )
WHERE t.table_type = "base table"
AND t.table_name <> 'dtproperties'
You can also get the create view, procedure or trigger script by joining the
syscomments table to sysobjects.
> If you're using MS SQL server, you can also run the following SQL from
> Query
> Analyzer:
> -- get all tables for a db
> select left(so.name, 27) as tableName
> ,left(sc.name, 20) as columnName
> ,left(st.name, 10) as dataType
> ,sc.length as dataLength
> from sysobjects so
> ,syscolumns sc
> ,systypes st
> where so.id = sc.id
> and sc.xtype = st.xtype
> and so.xtype = 'U'
> order by so.name, sc.name
> -- get all stored procedures for a db
> SELECT sysobjects.name as name
> ,sysusers.name as PROCEDURE_SCHEMA
> FROM sysobjects
> ,sysusers
> WHERE sysobjects.uid = sysusers.uid
> AND sysobjects.type = 'P'
> ORDER BY name
> When I'm developing I use the first one all the time to tell me about the
> tables and datatypes and data sizes for the fields, etc... it's very
> useful,
> and a lot faster than messing around with the Enterprise manager. Usually
> I
> just run the top SQL statement when I start working on a db, and
> copy/paste
> everything into a text editor (UltraEdit) so I don't have to keep running
> it. Every once in a while I need to adjust the "left()" statements to get
> a
> long name or something, but, all-in-all, these values work really well.
> Jeff Polaski
> "The cow is of the bovine ilk;
> One end is moo, the other, milk."
> -- Ogden Nash
> -----Original Message-----
> From: James Johnson [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 10, 2002 4:26 PM
> To: CF-Talk
> Subject: Get List of tables in a DB
> Hi,
> Is there a CF function or SQL statement that returns a list of table in
> an Access DB?
> Thanks
> *******************************************
> James Johnson
> SMB-Studios
> Innovative Online Learning for Spirit, Mind and Body
> www.smb-studios.com
> [EMAIL PROTECTED]
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
