db_attr

Description

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.

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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute db_attr.