You can usually get that info from the database's system tables, depending
on your RDBMS. (In the following queries, 'typename' will give you the
datatype.)
MSSQL:
SELECT syscolumns.colid,
syscolumns.name,
syscolumns.colorder AS sortorder,
syscolumns.prec AS collen,
syscolumns.xtype,
syscolumns.typestat,
syscolumns.xusertype,
syscolumns.isnullable,
systypes.name AS typename,
( SELECT COUNT(*)
FROM sysindexkeys
WHERE id = syscolumns.id
AND indid = 1
AND colid = syscolumns.colid
) AS isPrimary
FROM syscolumns LEFT JOIN
systypes ON syscolumns.xtype = systypes.xtype
AND syscolumns.xusertype = systypes.xusertype
WHERE id = OBJECT_ID(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#arguments.tableName#">)
ORDER BY colorder
Oracle:
SELECT table_name colid,
column_name name,
column_id sortorder,
data_length collen,
nullable isnullable,
data_type typename,
( SELECT COUNT(*)
FROM user_constraints c INNER JOIN
user_cons_columns cc ON c.constraint_name =
cc.constraint_name
WHERE c.table_name = <cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="#UCase(arguments.tableName)#">
AND c.constraint_type = <cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="P">
AND cc.column_name = user_tab_columns.column_name
) AS isPrimary
FROM user_tab_columns
WHERE table_name = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#UCase(arguments.tableName)#">
ORDER BY column_id
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331186
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
