Skip to content

db-id-transact-sql.md

Latest commit

 

History

History
122 lines (98 loc) · 4.51 KB

db-id-transact-sql.md

File metadata and controls

122 lines (98 loc) · 4.51 KB

title: "DB_ID (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "07/30/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.component: "t-sql|functions" ms.reviewer: "" ms.suite: "sql" ms.technology: t-sql ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:

  • "DB_ID_TSQL"
  • "DB_ID" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "viewing database ID numbers"
  • "IDs [SQL Server], databases"
  • "database IDs [SQL Server]"
  • "identification numbers [SQL Server], databases"
  • "displaying database ID numbers"
  • "DB_ID function" ms.assetid: 7b3aef89-a6fd-4144-b468-bf87ebf381b8 caps.latest.revision: 39 author: edmacauley ms.author: edmaca manager: craigg monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

DB_ID (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

Returns the database identification (ID) number.

Topic link icon Transact-SQL Syntax Conventions

Syntax

DB_ID ( [ 'database_name' ] )

Arguments

'database_name'
Is the database name used to return the corresponding database ID. database_name is sysname. If database_name is omitted, the current database ID is returned.

Return types

int

Permissions

If the caller of DB_ID is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.

Important

By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.

Examples

A. Returning the database ID of the current database

The following example returns the database ID of the current database.

SELECT DB_ID() AS [Database ID];  
GO

B. Returning the database ID of a specified database

The following example returns the database ID of the [!INCLUDEssSampleDBobject] database.

SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];  
GO

C. Using DB_ID to specify the value of a system function parameter

The following example uses DB_ID to return the database ID of the [!INCLUDEssSampleDBobject] database in the system function sys.dm_db_index_operational_stats. The function takes a database ID as the first parameter.

DECLARE @db_id int;  
DECLARE @object_id int;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');  
IF @db_id IS NULL   
  BEGIN;  
    PRINT N'Invalid database';  
  END;  
ELSE IF @object_id IS NULL  
  BEGIN;  
    PRINT N'Invalid object';  
  END;  
ELSE  
  BEGIN;  
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);  
  END;  
GO

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

D. Return the ID of the current database

The following example returns the database ID of the current database.

SELECT DB_ID();

E. Return the ID of a named database.

The following example returns the database ID of the AdventureWorksDW2012 database.

SELECT DB_ID('AdventureWorksPDW2012');

See also

DB_NAME (Transact-SQL)
Metadata Functions (Transact-SQL)
sys.databases (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)