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.
<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
">
cfinsert, cfprocparam, cfprocresult, cfqueryparam, cfstoredproc, cftransaction, cfupdate; "Optimizing database use" 37 in the ColdFusion Developer's Guide.
ColdFusion 8: Added this tag.
Attribute |
Req/Opt |
Default |
Description |
---|---|---|---|
datasource |
Required |
|
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:
|
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. |
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. |
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. |
<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>