DB_ID (Transact-SQL)

DB_ID (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the database identification (ID) number.

Topic link icon Transact-SQL Syntax Conventions


DB_ID ( [ 'database_name' ] ) 
-- Azure SQL Data Warehouse and Parallel Data Warehouse
DB_ID ( [ 'database_name' ] ); 

'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.

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.

System_CAPS_security Security Note

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.

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

SELECT DB_ID() AS [Database ID];
GO

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

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

The following example uses DB_ID to return the database ID of the AdventureWorks2012 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

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

SELECT DB_ID();

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

SELECT DB_ID('AdventureWorksPDW2012');

Community Additions

ADD
Show:
© 2016 Microsoft