Version Agnostic SQL Server Script/Statement that detects existence of a specific database

Question

Due to the packaged nature of the release, a SQL Server script (well more of a statement) needs to be created that can execute correctly on SQL Server 7.0 thru 2008 which can essentially achieve this:

if exists(select * from sys.databases where name = 'Blah')

Reasons this is difficult.

SQL 7 'sys.databases' is not valid

SQL 2008 'sysdatabases' is not valid

I stupidly parsed out the version number using serverproperty, to allow an IF depending on the version:

if (select CONVERT(int,replace(CONVERT(char(3),serverproperty ('productversion')),'.',''))) >= 80

Then discovered serverproperty does not exist under SQL 7.

Note that the SQL can be remote from the install, so no futzing around on the local machine - reg entries/file versions etc is of any use.

SQL Server error handling (especially 7.0) is poor, or maybe I don't understand it well enough to make it do a kind of try/catch.

I am now getting problem-blindness to this, so any pointers would be appreciated.

Thanks.

Gareth

Answer 1

Try

USE database

and test @@ERROR.

USE database
IF @@ERROR <> 0 GOTO ErrExit

logic ...

RETURN 0

ErrExit:
RETURN 1

(or RAISERROR, or ...)

Answer 2

You could try a TRY... CATCH around a USE [DatabaseName].

Answer 3

Thanks Le Dorfier. It still has the issue that an error is raised and not handled by the GOTO:

Msg 1801, Level 16, State 3, Line 9 Database 'DWCHServer' already exists. Choose a different database name.

I even tried

DECLARE @err int USE DWCHServer

SELECT @err = @@error IF @err <> 0 GOTO ErrExit

I was trying to avoid this, as it may well have implications, depending on the SQL box is set up. It might page someone or something. I doubt it, as it is a level 16 (Miscellaneous user error - nice) and it is only 17+ that usually kick off some kind of workflow.

Thinking of changing the system requirements to 2000+ instead ;)

Answer 4

I don't have access to a SQL 7 instance, but I encourage you to try:

sp_helpDB

I know this works on sql 2000 and sql 2005 to get a list of databases. I suspect it works on SQL 7, too.

Answer 5

Thanks G Mastros

This looks like it might yield a 100% solution. It is available under SQL 7.

I need to complete and test, but at first glance I think it will fly.

Here's the draft code FYI.

PS - not enough rep to vote you up.

Gareth

create table #dwch_temp ( name sysname ,db_size nvarchar(13) ,owner sysname ,dbid smallint ,created nvarchar(11) ,status nvarchar(600) ,compatibility_level tinyint ) go

insert into #dwch_temp exec sp_helpdb

if exists(select name from #dwch_temp where name = 'DWCHServer')

-- run the code

drop table #dwch_temp

Answer 6

sysdatabases is a remnant from the Sybase era and is still present in SQL 2008 (although deprecated). You can check for the existence of a database with a query like this:

IF EXISTS (SELECT 1 FROM master..sysdatabases where name = 'Blah')