1

When I run the query below, I get the database_id field for each database. I am under the impression that the value is being reused. For example, I delete databases, then I restore new databases. The database_id value for some of those newly restored databases may be an old one. Is that possible? Can that be changed?

The reason why I ask is the following. I am running a query across each DB in the SQL server. I would like to exclude some DBs and I am using the database_id field to identify those I wish to skip in the query. However, if a database_id is being reused, I must to update the query regularly. Thank you.

SELECT *
FROM sys.databases
DeepDiver
  • 127
  • 1
  • 2
  • 12
  • It's not persistent so it can be changed. – Ilyes Jun 09 '19 at 14:54
  • `database_id` values may be reused as databases are dropped/created/restored. This is not configurable behavior. Consider adding why you care about this to your question so someone can suggest a solution. – Dan Guzman Jun 09 '19 at 15:00
  • @DanGuzman thank you, I edited the question and presented the scenario. Sounds like I should use the database name instead. – DeepDiver Jun 09 '19 at 15:09
  • @DeepDiver, I agree it would be better to use the name. – Dan Guzman Jun 09 '19 at 15:12
  • @DeepDiver As I say before do not use the `database_id`, imaging you have A db and you delete it, then create B db and create also A db. Then you are in trouble if you use `database_id` – Ilyes Jun 09 '19 at 15:17
  • @Sami thanks to you and DanGuzman, it is all very clear, now. Learnt something new. – DeepDiver Jun 09 '19 at 15:29

2 Answers2

2

Database_id could change so you should use DB_ID function to get correct value before doing any type of comparison.

DB_ID

This function returns the database identification (ID) number of a specified database.

DB_ID ( [ 'database_name' ] )

For instance:

SELECT *
FROM sys.master_files
WHERE database_id = DB_ID('my_database_name');

-- instead of
SELECT *
FROM sys.master_files
WHERE database_id = 18;  -- database_id may not exists or point to different db
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Thank you, I had something similar to `SELECT * FROM sys.databases WHERE database_id NOT IN (6,7,8,11,19)` but clearly cannot do that. – DeepDiver Jun 09 '19 at 15:31
0

Here is what I will do and solves my situation.

Using the solution suggested in this thread, I will run a subquery and create a list of all databases where the target table exists. Then, I run my query across all those DBs available in that list.

DeepDiver
  • 127
  • 1
  • 2
  • 12