This is also some cool tool you can use to retrieve the database ids for all databases on your server. I like writing this type of scripts because they come in handy and can help stop someones headache one day. Now, the logic l’m going to use is to find the maximum database id on my server and loop from beginning to the end and write out the name of each database at that id, if a database is dropped, their id is not printed. That should make sense. I’ll break the whole program into steps
Step 1.Write a script that performs the following tasks:
a. Define a variable to hold the maximum database ID value.
b. Set that variable equal to the maximum database ID value. Use the database_id of the sys.databases table in master.
USE MASTER
GO
DECLARE @maxDbId INT
SELECT @maxDbId=Max(database_id)
FROM sys.databases
Step 2. Write a script that performs the following tasks. The script will need to use variables and WHILE.
a.Perform b. and c. for each database. Start with the database having database ID of 1, then 2, and
continue until you reach the maximum database ID value. Use your code from step 2 to determine the
value for the maximum database ID. Your loop should stop there.
b.Determine the name of the database (and cause a variable of datatype sysname to have a value of that name).
Hint: Use the DB_NAME function.
c.Construct a character string that says ‘Database ID <value> is <database_name>’.
(For example: Database ID 1 is master.) A variable should hold this string as its value.
Use PRINT to print the variable.*/
DECLARE @maxDbId INT, @databasename sysname, @counter int = 1
SELECT @maxDbId=Max(database_id)
FROM sys.databases
WHILE(@counter <= @maxDbId)
BEGIN
SELECT @databasename = DB_NAME(@counter)
PRINT ‘Database ID of ‘ + cast(@counter as varchar(10)) + ‘ is ‘ + @databasename
SET @counter = @counter + 1
END
Step 3.Modify your script for step 2 so that if a database has been dropped,
you will not print a result for that database ID value.
DECLARE @maxDbId INT, @databasename sysname, @counter int,@check int
SET @counter = 1
SELECT @maxDbId=Max(database_id)
FROM sys.databases
WHILE(@counter <= @maxDbId)
BEGIN
SELECT @check = database_id
FROM sys.databases
WHERE database_id = @counter
IF (@check IS NOT NULL)
BEGIN
SELECT @databasename = DB_NAME(@counter)
PRINT ‘Database ID of ‘ + cast(@counter as varchar(10)) + ‘ is ‘ + @databasename
END
SET @counter = @counter + 1
END
Lets see the final run on my sql server
Database ID of 1 is master
Database ID of 2 is tempdb
Database ID of 3 is model
Database ID of 4 is msdb
Database ID of 5 is ReportServer
Database ID of 6 is ReportServerTempDB
Database ID of 7 is AdventureWorksDW2008R2
Database ID of 8 is AdventureWorksLT2008R2
Database ID of 9 is AdventureWorks
Database ID of 10 is AdventureWorksDW
Database ID of 11 is AdventureWorksLT
Database ID of 12 is SSASlogging
Database ID of 13 is VinOLTP
Database ID of 14 is VinOLAP
Database ID of 15 is VinPrestaging
Database ID of 16 is VinStaging
Database ID of 17 is handsondatamart
Database ID of 18 is MY_ETL_WORK
Database ID of 19 is NewStaging_Purchase
Database ID of 20 is NewOLTP_Purchase
Database ID of 21 is NewPurchaseDb
Database ID of 22 is staging
Database ID of 23 is OLTP
Database ID of 24 is SampleDb
Database ID of 25 is SSIS_Audit_DB
Database ID of 26 is sysdb
Database ID of 27 is Superstore
Enjoy, Questions and comments welcome.
Jah bless
