Check if database exists?

Hello!

I tried to search if this had been asked before but didn't found anything so I hope It has not been 100 times before me.

Is it possible to check if a database allready exist in the SQL Server 2005? If for example I want to create a database called "Testing" I first want to check if the database exists before I create it.

Thanks in advance!Try:

if not exists(select * from sys.databases where name = 'Testing')
create database testing

Hope this helps.

Niels|||Thank you, that worked great, now I just have to figure out how to check if a table exists in the database =)|||

You can use the various metadata functions.

if db_id('dbname') is not null

if object_id('object_name', 'U') is not null -- for table

if object_id('object_name', 'P') is not null -- for SP

These are much easier to read and use than queries against system tables/views. See SQL Server 2005 Books Online for more details on the object_id function.

|||

The problem with all of your methods for checking table (or object) existence, is that it will only check in the current database for the object.

Here is a SPROC that will check another database for the existence of an object:

CREATE PROCEDURE [dbo].[ObjectCheck_sp]
@.DB SysName,
@.Name SysName,
@.Type nvarchar(1000)
AS
BEGIN
SET NOCOUNT ON ;

DECLARE @.Cmd NVARCHAR(MAX)
Declare @.Out Table ( ResultSet XML )

IF LEFT(@.Type, 1) != '('
SET @.Type = '(' + CHAR(39) + @.Type + CHAR(39) + ')'

Set @.CMD = 'Select * from [' + @.DB + '].sys.objects where name=' + CHAR(39) + @.Name + CHAR(39) + ' and type in ' + @.Type + ' For XML Auto,Type'

Insert into @.Out
EXEC ( @.CMD )

IF EXISTS ( Select *
from @.Out
where ResultSet is not null )
RETURN 1
ELSE
RETURN 0
END -- Procedure: ObjectCheck_sp

An example call is:

Declare @.RetVal INT

exec @.RetVal=ObjectCheck_sp @.DB='master', @.Name='MSreplication_options', @.Type=N'( ''U'', ''V'')'

Select @.RetVal