DB_ID (Transact-SQL)
Applies to:
SQL Server (all supported versions)
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This function returns the database identification (ID) number of a specified database.
Transact-SQL Syntax Conventions
Syntax
DB_ID ( [ 'database_name' ] )
Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
'database_name'
The name of the database whose database ID number DB_ID will return. If the call to DB_ID omits database_name, DB_ID returns the ID of the current database.
Return types
int
Remarks
DB_ID may only be used to return the database identifier of the current database in Azure SQL Database. NULL is returned if the specified database name is other than the current database.
Note
When used with Azure SQL Database, DB_ID may not return the same result as querying database_id from sys.databases. If the caller of DB_ID is comparing the result to other sys views, then sys.databases should be queried instead.
Permissions
If the caller of DB_ID does not own a specific non-master or non-tempdb database, ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions at minimum are required to see the corresponding DB_ID row. For the master database, DB_ID needs CREATE DATABASE permission at minimum. The database to which the caller connects will always appear in sys.databases.
Important
By default, the public role has the VIEW ANY DATABASE permission, which allows all logins to see database information. To prevent a login from detecting 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
This 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
This example returns the database ID of the AdventureWorks2019 database.
SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];
GO
C. Using DB_ID to specify the value of a system function parameter
This example uses DB_ID to return the database ID of the AdventureWorks2019 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: Azure Synapse Analytics and Analytics Platform System (PDW)
D. Return the ID of the current database
This example returns the database ID of the current database.
SELECT DB_ID();
E. Return the ID of a named database.
This 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)