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

    You need to talk to Tim and Adam, mate 🙂

  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?

  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!

  4. jeff Avatar

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

  5. Jay Avatar

    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.

  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

  7. Rick Root Avatar
    Rick Root

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

  8. Shaun Avatar

    Here is a sample output for columns:
    1 0 [empty string] id 10 0 NO NO YES 1 N/A N/A [empty string] int identity

  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)?


Leave a Reply