# DBInfo

Retrieve database metadata for a given datasource.

## Component Signature

```
<bx:DBInfo type=[string]
name=[string]
datasource=[string]
table=[string]
pattern=[string]
dbname=[string]
filter=[string] />
```

### Attributes

| Atrribute    | Type     | Required | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | Default |
| ------------ | -------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| `type`       | `string` | `true`   | Type of metadata to retrieve. One of: `columns`, `dbnames`, `tables`, `foreignkeys`, `index`, `procedures`, or `version`.                                                                                                                                                                                                                                                                                                                                                                                                                                                         |         |
| `name`       | `string` | `true`   | Name of the variable to which the result will be assigned. Required.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |         |
| `datasource` | `string` | `false`  | Name of the datasource to check metadata on. If not provided, the default datasource will be used.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |         |
| `table`      | `string` | `false`  | Table name for which to retrieve metadata. Required for `columns`, `foreignkeys`, and `index` types.                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |         |
| `pattern`    | `string` | `false`  | Table name pattern to filter by. Can use wildcards or any `LIKE`-compatible pattern such as `tbl_%`. Can use `schemaName.tableName` syntax to additionally filter by schema.                                                                                                                                                                                                                                                                                                                                                                                                      |         |
| `dbname`     | `string` | `false`  | Name of the database to check for tables. If not provided, the database name from the connection will be used.                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |         |
| `filter`     | `string` | `false`  | <p>This is a string value that must match a table type in your database implementation. Each database is different.<br>Some common filter types are:<br></p><ul><li><br>TABLE - This is the default value and will return only tables.<br>VIEW - This will return only views.<br>SYSTEM TABLE - This will return only system tables.<br>GLOBAL TEMPORARY - This will return only global temporary tables.<br>LOCAL TEMPORARY - This will return only local temporary tables.<br>ALIAS - This will return only aliases.<br>SYNONYM - This will return only synonyms.<br></li></ul> |         |

## Examples

### Check JDBC Version

```js
bx:dbinfo( type='version', name='result' );
```

The `result` variable is now populated with a query looking something like this:

| DATABASE\_PRODUCTNAME | DATABASE\_VERSION | DRIVER\_NAME      | DRIVER\_VERSION | JDBC\_MAJOR\_VERSION | JDBC\_MINOR\_VERSION |
| --------------------- | ----------------- | ----------------- | --------------- | -------------------- | -------------------- |
| MySQL                 | 8.0.33            | MySQL Connector/J | 8.0.33          | 4                    | 2                    |

### Read All Database Views

Use `type=tables` to read all database tables, or filter for a specific type of table using `filter=TABLE|VIEW|SYSTEM TABLE|GLOBAL TEMPORARY|LOCAL TEMPORARY`:

```js
bx:dbinfo( type='tables', name='result', filter='VIEW' );
```

| TABLE\_CAT | TABLE\_SCHEM | TABLE\_NAME | TABLE\_TYPE | REMARKS      | TYPE\_CAT | TYPE\_SCHEM | TYPE\_NAME | SELF\_REFERENCING\_COL\_NAME | REF\_GENERATION |
| ---------- | ------------ | ----------- | ----------- | ------------ | --------- | ----------- | ---------- | ---------------------------- | --------------- |
| mydb       | public       | my\_view    | VIEW        | Example view | NULL      | NULL        | NULL       | NULL                         | NULL            |

### Read Table Columns

```js
bx:dbinfo( type='columns', name='result', table='admins' );
```

The `admins` table column information is now populated into the `result` variable. The result columns are:

* TABLE\_CAT
* TABLE\_SCHEM
* TABLE\_NAME
* COLUMN\_NAME
* DATA\_TYPE
* TYPE\_NAME
* COLUMN\_SIZE
* BUFFER\_LENGTH
* DECIMAL\_DIGITS
* NUM\_PREC\_RADIX
* NULLABLE
* REMARKS
* COLUMN\_DEF
* SQL\_DATA\_TYPE
* SQL\_DATETIME\_SUB
* CHAR\_OCTET\_LENGTH
* ORDINAL\_POSITION
* IS\_NULLABLE
* SCOPE\_CATALOG
* SCOPE\_SCHEMA
* SCOPE\_TABLE
* SOURCE\_DATA\_TYPE
* IS\_AUTOINCREMENT
* IS\_GENERATEDCOLUMN
* SCOPE\_CATLOG
* IS\_PRIMARYKEY
* IS\_FOREIGNKEY
* REFERENCED\_PRIMARYKEY
* REFERENCED\_PRIMARYKEY\_TABLE

### Output Column Names

Along with the data type and size

```java
<bx:dbinfo type="columns" name="cols" table="tester">
<bx:output query="cols">
        #cols.COLUMN_NAME# #cols.TYPE_NAME#(#cols.COLUMN_SIZE#)<br>
</bx:output>

```