Thursday, March 8, 2012

Control if a SQL database exists before its creation or deletion

Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]

I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]

but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much

You can try:

if db_id('myDb') is not null print 'DB exists'

Alter NULL, NOT NULL check as appropriate for what you want to check

/Kenneth

|||Ok it functions correctly, thank you KeWin

No comments:

Post a Comment