checking if a database exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

checking if a database exists

Author

Topic

idoyohanan
Starting Member

3 Posts

Posted - 2009-10-14 : 06:11:13

Hi,

I have a script like that and I want to add in the start two lines to check if a database exist, don't do all that script. how can I do that ?

I thought about something like that (but it doesn't work):
if exists (select 1 from sysdatabases where name = 'Beacon')
return

use master

if not exists (select 1 from sysdatabases where name = 'A')
begin
create database Beacon
end
GO

use A
------------------------------------------ CREATE TABLES ---------------------------------------------------

...
GO

...
GO

...
GO

...
GO

...
GO

-------------------------------------CREATE STORED PROCEDURES--------------------------------------------------------------

...
GO

...
GO

---------------------------------------------------------------------------------------------------------------

...
GO

idoyohanan
Starting Member

3 Posts

Posted - 2009-10-14 : 06:14:32

I had a mistake in the first lines:

use master

if not exists (select 1 from sysdatabases where name = 'A')
begin
create database A
end
GO

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-14 : 12:43:09

If you run your script with an account that is in the sysadmin role, you can use raiserror with severity 20 or above to terminate the connection.

if not exists (select 1 from master.dbo.sysdatabases where name = 'Beacon')
	begin
	raiserror ( 'Database Beacon does not exist',22,1) with log
	end

Results:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

CODO ERGO SUM

idoyohanan
Starting Member

3 Posts

Posted - 2009-10-22 : 06:05:14

Hi,

I don't want to raise an error.

Is that script as it written (in my first topic) runs even though the database exists ? (from the use master until the end).

if yes - than how can I put in one block the lines under the condition (what replaces the { } to define a block in sql ?). this in order that the code in the block will be executed only if the database not exists ?

use master

if not exists (select 1 from sysdatabases where name = 'A')
{
begin
create database Beacon
end
GO

use A
------------------------------------------ CREATE TABLES ---------- -----------------------------------------

...
GO

...
GO

...
GO

...
GO

...
GO

-------------------------------------CREATE STORED PROCEDURES--------------------------------------------------------------

...
GO

...
GO

---------------------------------------------------------------------------------------------------------------

...
GO
}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources