<cfdbinfo>

edit

Lets you retrieve information about a data source, including details about the database, tables, queries, procedures, foreign keys, indexes, and version information about the database, driver, and JDBC.

This tag supports only JDBC drivers, and does not support ODBC-based drivers, including the Microsoft Access driver.

This tag cannot have a body.

This tag is also supported within <cfscript>

<cfdbinfo datasource=object name=string type=string procedure=string dbname=string password=string pattern=string table=string username=string Filter=string >
Attribute Description
datasource
object, optional 		edit

Datasource to use to connect to the database.
name
string, required 		edit

Name to use to refer to the result.

Alias: variable
type
string, required 		edit

Type of information to get:

  • dbNames: database name and type
  • tables: information to all tables
  • columns: column information for tables/views, accepts wildcards like '%''
  • columns_minimal: column information for tables/views, but no FK / PK information (much faster)
  • version: version information of the database
  • procedures: information to all procedures
  • procedure_columns: column information to a single procedure
  • foreignKeys: information to all foreignKeys
  • index: information to all indexes
  • users: list database users
  • terms: vendor preferred term for PROCEDURE, CATALOG and SCHEMA
dbname
string, optional 		edit

Name of the database

Alias: dbnames
password
string, optional 		edit

Password to connect to the database.
username
string, optional 		edit

User name to connect to the database.

Database Names

type="dbNames" - Get database name and type

Attribute Description
pattern
string, optional 		edit

Specifies a sql filter, i.e sys% to retrieve information about specific tables, columns, or stored procedures

Table Information

type="tables" - Get information about all tables

Attribute Description
pattern
string, optional 		edit

Specifies a sql filter, i.e sys% to retrieve information about specific tables, columns, or stored procedures
Filter
string, optional 		edit

Filter for type="tables" with a wildcard, defaults to all types when empty (slow, especially oracle).

  • "TABLE"
  • "VIEW"
  • "SYSTEM TABLE"
  • "GLOBAL TEMPORARY"
  • "LOCAL TEMPORARY"
  • "ALIAS"
  • "SYNONYM"

Note: Each Database implementation has it's own supported types

Introduced: Lucee 6.0.0.76

Alias: tabletype

Introduced: 6.0.0.73

Column Information

type="columns" - Get column info with FK/PK relationships

Attribute Description
table
string, optional 		edit

Name of the table from which you retrieve information.

Patterns are also supported, i.e.

  • use % to fetch all columns, from every table.
  • use SCHEMA.% to fetch all columns, from every table, from a scheme.

Not used for type="tables", use pattern instead

Column Info (Fast)

type="columns_minimal" - Get column info without FK/PK (much faster)

Attribute Description
table
string, optional 		edit

Name of the table from which you retrieve information.

Patterns are also supported, i.e.

  • use % to fetch all columns, from every table.
  • use SCHEMA.% to fetch all columns, from every table, from a scheme.

Not used for type="tables", use pattern instead

Version Information

type="version" - Get database version info

Attribute Description

Stored Procedures

type="procedures" - Get information about all procedures

Attribute Description
pattern
string, optional 		edit

Specifies a sql filter, i.e sys% to retrieve information about specific tables, columns, or stored procedures

Procedure Columns

type="procedure_columns" - Get column info for a procedure

Attribute Description
procedure
string, optional 		edit

Name of the procedure from which you retrieve information.

Foreign Keys

type="foreignKeys" - Get foreign key information

Attribute Description
table
string, optional 		edit

Name of the table from which you retrieve information.

Patterns are also supported, i.e.

  • use % to fetch all columns, from every table.
  • use SCHEMA.% to fetch all columns, from every table, from a scheme.

Not used for type="tables", use pattern instead

Index Information

type="index" - Get index information

Attribute Description
table
string, optional 		edit

Name of the table from which you retrieve information.

Patterns are also supported, i.e.

  • use % to fetch all columns, from every table.
  • use SCHEMA.% to fetch all columns, from every table, from a scheme.

Not used for type="tables", use pattern instead

Database Users

type="users" - List database users

Attribute Description

Vendor Terms

type="terms" - Get vendor preferred terminology

Attribute Description

Usage Notes

edit

Performance tips

  • Using type="columns_minimal" is dramatically faster than type="columns"
  • With type="table" using filter="table" is also much faster

Examples

edit

CFDBINFO Syntax

<cfdbinfo type="columns" name="db_info" datasource="lucee" dbname="tester" table="test">

See also