db_attr

Returns the durability, dml_logging, and template settings, and compression level for the specified database.

Syntax

db_attr('database_name' | database_ID | NULL, 'attribute')

Parameters

database_name – name of the database.
database_ID – ID of the database
NULL – if included, db_attr reports on the current database
attribute – is one of:
- help – display db_attr usage information.
- durability – returns durability of the given database: full, at_shutdown, or no_recovery.
- dml_logging – returns the value for data manipulation language (DML) logging for specified database: full or minimal.
- template – returns the name of the template database used for the specified database. If no database was used as a template to create the database, returns NULL.
- compression – returns the compression level for the database.
- list_dump_fs – identifies the features to be included in future dumps.You may not be able to load a database or transaction dumps that are generated in a later version into an earlier version. The features that are in use in a database, and objects that are created using newer features, are captured in database and transaction dumps. Before generating such dumps, use list_dump_fs to identify the features to be included in future dumps.

Examples

Example 1 – Returns the syntax for db_attr:

select db_attr(0, "help")

-----------------------------------------------------
Usage: db_attr('dbname' | dbid | NULL, 'attribute')
List of options in attributes table:
         0 : help
         1 : durability
         2 : dml_logging
         3 : template
         4 : compression

Example 2 – Selects the name, durability setting, dml_logging setting and template used from sysdatabses:

select  name = convert(char(20), name), 
durability = convert(char(15), db_attr(name,     "durability")),
     dml_logging = convert(char(15), db_attr(dbid,     "dml_logging")),
     template = convert(char(15), db_attr(dbid, "template"))
from sysdatabases

name              durability         dml_logging                 template
----------------   ----------------   ------------------------    --------
master             full               full                        NULL
model              full               full                        NULL
tempdb             no_recovery        full                        NULL 
sybsystemdb        full               full                        NULL   
sybsystemprocs     full               full                        NULL
repro              full               full                        NULL
imdb               no_recovery        full                        db1
db                 full               full                        NULL
at_shutdown_db     at_shutdown        full                        NULL
db1                full               full                        NULL
dml                at_shutdown        minimal                     NULL

Example 3 – Runs db_attr against the DoesNotExist database, which does not exist:
```
select db_attr("DoesNotExist", "durability")
```
```
-----------------------------------------------------
NULL 
```
Example 4 – Runs db_attr against a database with an ID of 12345, which does not exist:
```
select db_attr(12345, "durability")
```
```
-----------------------------------------------------
NULL 
```

Example 5 – Runs db_attr against an attribute that does not exist:

select db_attr(1, "Cmd Does Not Exist")

-----------------------------------------------------
NULL

Example 6 – Returns the various features that are in use for the pubs2 database, and the target server version, which can safely load such dumps. The last line in bold indicates that the optimized data load with parallel index updates was executed in this database, and is contained in the transaction log.
```
1> select db_attr('pubs2', 'list_dump_fs')
2> go

Features found active in the database that will be recorded in a subsequent dump header:

ID= 3: 15.7.0.007: Database has compressed tables at version 1
ID= 4: 15.7.0.000: Database has system catalog changes made in 15.7 GA
ID= 7: 15.7.0.020: Database has system catalog changes made in 15.7 ESD#02
ID=11: 15.7.0.100: Database has the Sysdams catalog
ID=13: 15.7.0.100: Database has indexes sorted using RID value comparison
ID=14: 15.7.0.110: Log has transactions generating parallel index operations
```
Future dumps of pubs2 will be loadable only in the target server version indicated. To load the dumps of such a database in a target version that is earlier than the version listed, downgrade the database to remove the footprint of newer features.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute db_attr.