SQL server: getting database names and IDs
Posted by jpluimers on 2021/06/29
A few statements go get database names and IDs based on these functions or system tables:
- [WayBack]
DB_NAME()function (Transact-SQL) – SQL Server | Microsoft Docs: This function returns the name of a specified database. - [WayBack]
DB_ID()function (Transact-SQL) – SQL Server | Microsoft Docs: This function returns the database identification (ID) number of a specified database. - [WayBack]
sys.sysdatabasesview (Transact-SQL) – SQL Server | Microsoft Docs: Contains one row for each database in an instance of Microsoft SQL Server. When SQL Server is first installed, sysdatabases contains entries for themaster,model,msdb, andtempdbdatabases.
Part of it has the assumption that a master database always exists.
-- gets current database name select db_name() as name ; name -------------------------------------------------------------------------------------------------------------------------------- acc (1 row affected)
-- gets current database ID select db_id() as dbid ; dbid ------ 5 (1 row affected)
-- gets all database IDs and names select dbid,name from sys.sysdatabases ; dbid name ------ -------------------------------------------------------------------------------------------------------------------------------- 1 master 5 acc (2 rows affected)
-- gets current database name by ID select db_name(db_id()) as name ; name -------------------------------------------------------------------------------------------------------------------------------- acc (1 row affected)
-- gets case corrected database name for sys.sysdatabases.name having a case insensitive collation sequence select dbid,name from sys.sysdatabases where name='Master' ; dbid name ------ -------------------------------------------------------------------------------------------------------------------------------- 1 master (1 row affected)
-- gets case corrected database name for sys.sysdatabases.name having a case sensitive collation sequence select dbid,name from sys.sysdatabases where name = 'Master' collate Latin1_General_100_CI_AI ; dbid name ------ -------------------------------------------------------------------------------------------------------------------------------- 1 master (1 row affected)
Note that:
- even though by default the SQL server collation sequence is case insensitive, it can make sense to do a case insensitive search, for example by using the
upperfunction, specifying a collation, or casting to binary. I likeupperthe most, because – though less efficient – it is a more neutral SQL idiom.- the most neutral case insensitive collation seems to be
Latin1_General_100_CI_AI
Related:
- [WayBack] SQL server ignore case in a where expression – Stack Overflow answered by Solomon Rutzky, summarised as:
- Do not use
upperasupperwithlowerdoes not always round-trip. - Do not use
varbinaryas it is not case insensitive. - Neither the
=orlikeoperators are case sensitive by default: both need acollateclause. - Find the collation of the column(s) involved; if it contains
_CI, then you are done (it is already case insensitive); if it contains_CS, then replace that with_CI(case insensitive) and add that in acollateclause. - Collations are per
predicate, so notper query,per table,per columnnorper database. This means you have to specify them if you want to use a different one than the default.
- Do not use
- [WayBack] What is Collation in Databases? | Database.Guide
Latin1_General_100_CI_AILatin1-General-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive - [WayBack] Collation Info: Information about Collations and Encodings for SQL Server
- [WayBack] SQL Instance Collation – Language Neutral Required:
I recommend using
Latin1_General_100_CI_AI. I recommend this because:…
- If
Latin1_General_CI_AIis supported, then there’s almost no chance thatLatin1_General_100_CI_AI(which is a far better choice) isn’t also supported. The version 100 collation has about 15,400 more sort weight definitions, plus 438 more uppercase/lowercase mappings. Not having those sort weights means that 15,400 more characters in the non-100 version equate to space, an empty string, and to each other. Not having those case mappings means that 438 more characters in the non-100 version return the character passed in (i.e. no change) for theUPPER()andLOWER()functions. There is no reason at all to wantLatin1_General_CI_AIinstead ofLatin1_General_100_CI_AI. There might be a need if code was put into place to work around these deficiencies, and that code would behave incorrectly under the newer, better version of that collation. However, it’s highly unlikely that code was put into place to account for this, and extremely unlikely that if such code did exist, that it would error or doing things incorrectly due to the newer collation.
- If
- [WayBack] Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2) – Sql Quantum Leap
- [WayBack] How to do a case sensitive search in WHERE clause (I’m using SQL Server)? – Stack Overflow answered by Jonas Lincoln:
By using collation or casting to binary, like this:
SELECT * FROM Users WHERE Username = @Username COLLATE SQL_Latin1_General_CP1_CS_AS AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS AND Username = @Username AND Password = @PasswordThe duplication of username/password exists to give the engine the possibility of using indexes. The collation above is a Case Sensitive collation, change to the one you need if necessary.
The second, casting to binary, could be done like this:
SELECT * FROM Users WHERE CAST(Username as varbinary(100)) = CAST(@Username as varbinary)) AND CAST(Password as varbinary(100)) = CAST(@Password as varbinary(100)) AND Username = @Username AND Password = @Password - [WayBack] sql – How to get Database name of sqlserver – Stack Overflow
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a Reply