This is a feature first demonstrated in CT yesterday, a new tag in
Scorpio named <CFDBINFO> which does just that, it returns information about databases (and data sources, and tables, and columns, and stored procedures, and more). This first code snippet shows how to obtain a list of tables in a specified data source (using the default database):
<cfdbinfo type="tables" datasource="myDSN" name="tables">
To get column details you can do the following:
<cfdbinfo type="columns" datasource="myDSN" table="myTables" name="columns">
This would return a query containing column names, type, size, default values, whether it allows NULL values, key associations, and more.
As you can see, <CFDBINFO> accepts a TYPE attribute which tells it what information you want, and the following types are supported:
- columns: Returns column details for a specific table.
- dbnames: Returns the databases in a specified data source.
- foreignkeys: Returns foreign key name information, including associated primary key, and delete and update rules.
- index: Returns index specifics, including column details, page usage, and whether or not the index is unique.
- procedures: Returns available stored procedures.
- tables: Returns the names of tables within a specific database.
- version: Returns database drive version details.
You need to talk to Tim and Adam, mate :)
nyaa nyaa CT!
My hunch is that the data in question can be selected from system tables that are kept for each DB (like the properties tables that MS SQL SERVER maintains for each DB).
So if the db user in question can select on those system tables, that will probably do it...
In an example setup, you might have a ms sql server DB named "myDB" and then a sql login w/username "MyDB-pubUser" with access permitted on that DB, with the roles of public and data-reader. data-reader would probably include the necessary SELECT rights on the sys tables, to allow this kind of introspection.
Then you'd just add the credentials for MyDB-pubUser on the datasource.
how this is accomplished with other RDBMS systems I can't say for sure; but consider in the good old days, running a "DESCRIBE" on a table or other object; my point being that the support for this kind of thing on the part of the RDBMS has been been around for some time.
Again this is just my hunch; there could be other equally tennable ways to get at the info. in question - i.e. maybe just using special SQL aggregate functions or the like.
Jeff, whatever permissions the data source has is what are used. Usually, DBMSs show you this information based on what your login has access to.
--- Ben
I feel so used.
CHAR_OCTET_LENGTH COLUMN_DEFAULT_VALUE COLUMN_NAME COLUMN_SIZE DECIMAL_DIGITS IS_FOREIGNKEY IS_NULLABLE IS_PRIMARYKEY ORDINAL_POSITION REFERENCED_PRIMARYKEY REFERENCED_PRIMARYKEY_TABLE REMARKS TYPE_NAME
1 0 [empty string] id 10 0 NO NO YES 1 N/A N/A [empty string] int identity
But why - O WHY - was it implemented as a tag (as opposed to a function, I mean)?
Sigh.
--
Adam