Ben Forta

AdobeStock_455007340

Scorpio Makes Obtaining Database And Table Info Easy

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.

9 responses to “Scorpio Makes Obtaining Database And Table Info Easy”

  1. Critter Avatar
    Critter

    ORLY?
    You need to talk to Tim and Adam, mate 🙂

    Reply
  2. Kurt Bonnet Avatar
    Kurt Bonnet

    Would love to see a cfdump of the type="columns" version of the tag call. Any chance you could post it?

    Reply
  3. Rick Root Avatar
    Rick Root

    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!

    Reply
  4. jeff Avatar
    jeff

    What level of db permissions will be needed for this tag to work?

    Reply
  5. Jay Avatar
    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.

    Reply
  6. Ben Forta Avatar
    Ben Forta

    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

    Reply
  7. Rick Root Avatar
    Rick Root

    Well then, Tim and Adam LIED TO US!
    I feel so used.

    Reply
  8. Shaun Avatar
    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

    Reply
  9. Adam Cameron Avatar
    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

    Reply

Leave a Reply