This is a feature first demonstrated in CT yesterday, a new tag in Scorpio named 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): To get column details you can do the following: 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, 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.
Scorpio Makes Obtaining Database And Table Info Easy
9 Comments
S
Shaun
Here is a sample output for columns:
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
R
Well then, Tim and Adam LIED TO US!
I feel so used.
B
Critter, Rick, nope - your event was an evening event, right? CT had too events that same day, a lunch time one and an evening one. So, I believe CT was indeed first. ;-)
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
J
Jay
re: level of DB perms -
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.
J
jeff
What level of db permissions will be needed for this tag to work?
R
Technically, Tim and Adam didn't DEMONSTRATE the tag so I guess Ben is technically correct, though we in sunny north carolina were the first non-NDA people to be told about it =)
nyaa nyaa CT!
K
Kurt Bonnet
Would love to see a cfdump of the type="columns" version of the tag call. Any chance you could post it?
C
ORLY?
You need to talk to Tim and Adam, mate :)
A
Adam Cameron
Nice bit of functionality.
But why - O WHY - was it implemented as a tag (as opposed to a function, I mean)?
Sigh.
--
Adam