how to display database structure with CF

[danrjohn]

I took over the support of a web page that was developed with coldfusion, and I like to know if there is an easy way to determine the database structure through a cf? I've done a google on this and come up with a suggestion of using

"SELECT *
FROM Information_Schema.Tables
ORDER BY Table_Name

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM Information_Schema.Columns
WHERE Table_Name = ‘#Table_Name#’

ORDER BY Ordinal_Position"

But I'm a bit confused on what are reserved names and what are not. Can someone help with this or is there an easier way to get this information?

Thanks!

 

[r937]

those are two separate SELECT statements

the first one will list all your tables

the second one will list all the columns for a specific table

do you have to do this via coldfusion? don't you have some other way to get at your database? like an SQL window in a GUI tool or something?

if so, run those queries there



r937.com | rudy.ca

 

[philhege]

What database? The syntax you posted doesn't work with every flavor.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[r937]

What database? The syntax you posted doesn't work with every flavor.

actually, the posted syntax will work in every database that supports the sql standard



r937.com | rudy.ca

 

[philhege]

So if I issue this to Access, it works?

 

[philhege]

(addendum) Just curious, because I got tired of messing with MSysObjects.

 

[r937]

not sure if it works in access or not

works in sql server, so at least we know that microsoft is well aware of the standard...

r937.com | rudy.ca