Reputation:

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

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

Upvotes: 2

Answers (5)

aristippus303

Reputation: 51

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.

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

Upvotes: 1

Paul Harrington

Reputation: 782

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')

Upvotes: 0