Friday, July 3, 2009

Is the current database a system one?

I finally got tired of accidentally polluting the master database with my tables and stored procedures: it can be very confusing when you're debugging something and you don't realize you're in the master database. I needed a simple check that the current database isn't one of the four system ones. Seems easy enough. But when I tried to find a way to do it in SQL Server 2005, this was the best I could find:

IF DB_NAME() IN ('master', 'tempdb', 'model', 'msdb') RAISERROR('   $$$ SYSTEM DB $$$', 20, 1) WITH NOWAIT, LOG

This is lame. I have to explicitly list them, and by name? I was really expecting something like sys.databases.is_ms_shipped, but no such luck. Seems like this would be a common need. (The error is at level 20 so that the script stops in its tracks, before it can do any damage.)