ColdFusion 9.0 Resources

cfdbinfo

Description

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.

Syntax

<cfdbinfo 
    datasource="data source name" 
    name="result name" 
    type="dbnames|tables|columns|version|procedures|foreignkeys|index" 
    dbname="database name" 
    password="password" 
    pattern="filter pattern" 
    table="table name" 
    username="username">

Note: You can specify this tag’s attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag’s attribute names as structure keys.

History

ColdFusion 8: Added this tag.

Attributes

Attribute	Req/Opt	Default	Description
`datasource`	Optional		Datasource to use to connect to the database.
`name`	Required		Name to use to refer to the result.
`type`	Required		Type of information to get: `dbnames`: database name and type `tables`: name, type, and remarks `columns`: name, SQL data type, size, decimal precision, default value, maximum length in bytes of a character or integer data type column, whether nulls are allowed, ordinal position, remarks, whether the column is a primary key, whether the column is a foreign key, the table that the foreign key refers to, the key name the foreign key refers to `version`: database product name and version, driver name and version, JDBC major and minor version `procedures`: name, type, and remarks `foreignkeys`: foreign key name and table, primary key name, delete, and update rules `index`: name, column on which the index is applied, ordinal position, cardinality, whether the row represents a table statistic or an index, number of pages used by the table or index, whether the index values are unique
`dbname`	Optional		Name of the database. Used only if the `action = "This overrides the one mentioned as a part of datasource definition."`
`password`	Optional		Password to connect to the database.
`pattern`	Optional		Used only if `type = "tables"`, `type = "columns"`, or `type = "procedures"`. Specifies a filter to retrieve information about specific tables, columns, or stored procedures. Use an underline (_) to represent a single wildcard character and a percent sign (%) to represent a wildcard of zero or more characters.
`table`	Required if `type = "columns" or type = "foreignkeys" or type = "index"`		Name of the table from which you retrieve information.
`username`	Optional	no	User name to connect to the database.

Usage

Use the cfdbinfo tag to return a query object that contains information about a database. The query object varies, depending on the value that you specify in the type attribute. The following table lists the query object contents for each type:

Type	Column name	Description
`dbnames`	DATABASE_NAME	Name of the database.
`dbnames`	TYPE	Type of the database, whether schema or catalog.
`tables`	TABLE_NAME	Name of the table.
	TABLE_TYPE	Type of the table, including view, table, synonym, and system table.
	REMARKS	Remarks of the table.
`columns`	COLUMN_NAME	Name of the column.
	TYPE_NAME	SQL data type of the column.
	IS_NULLABLE	Whether the column allows nulls.
	IS_PRIMARYKEY	Whether the column is a primary key.
	IS_FOREIGNKEY	Whether the column is a foreign key.
	REFERENCED_PRIMARYKEY	If the column is a foreign key, the name of the table it refers to.
	REFERENCED_PRIMARYKEY_TABLE	If the column is a foreign key, the key name it refers to.
	COLUMN_SIZE	Size of the column
	DECIMAL_DIGITS	Number of digits to the right of the decimal point.
	COLUMN_DEFAULT_VALUE	Default value of column.
	CHAR_OCTET_LENGTH	Maximum length in bytes of a character or integer data type column.
	ORDINAL_POSITION	Ordinal position of the column.
	REMARKS	Remarks of the column.
`version`	DATABASE_VERSION	Version of the database management system.
	DATABASE_PRODUCTNAME	Name of the database management system.
	DRIVER_VERSION	Version of the database driver.
	DRIVER_NAME	Name of the database driver.
	JDBC_MAJOR_VERSION	Major version number of the driver.
	JDBC_MINOR_VERSION	Minor version number of the driver.
`procedures`	PROCEDURE_NAME	Name of the stored procedure.
	REMARKS	Remarks for the stored procedure.
	PROCEDURE_TYPE	Procedure type, which indicates whether the procedure returns a result.
`foreignkeys`	FKCOLUMN_NAME	Foreign key name.
	FKTABLE_NAME	Foreign key table name.
	PKCOLUMN_NAME	Primary key name.
	DELETE_RULE	Specifies what action to take when you delete a record that has dependent records.
	UPDATE_RULE	Specifies what action to take when you update a record that has dependent records.
`index`	INDEX_NAME	Name of the index, empty if type is table statistic.
	COLUMN_NAME	Name of the column on which the index is applied, empty if the type is table statistic.
	ORDINAL_POSITION	Ordinal position.
	CARDINALITY	Number of unique values if the type is index, or number of rows if the type is statistic
	TYPE	Whether the row represents a table statistic or an index. Index types are clustered, hashed, or other.
	PAGES	Number of pages used by the table if the type is table statistic, or the number of pages used by the index.
	NON_UNIQUE	Whether the index values are unique.

Example

<cfset datasrc = "oratest"> 
 
<cfdbinfo  
    type="dbnames" 
    datasource="#datasrc#" 
    name="dbdata"> 
 
<cfoutput> 
The #datasrc# data source has the following databases:<br /> 
</cfoutput> 
<table border="1"> 
<tr> 
    <th valign="top" align="left">Database name</th><th>Type</th> 
</tr> 
    <cfoutput query="dbdata"> 
    <tr> 
        <td>#dbdata.DATABASE_NAME#</td><td>#dbdata.TYPE#</td> 
    </tr> 
    </cfoutput> 
</table>