0

Possible Duplicate:
How to check if a database exists in SQL Server?

I'm using Transact-SQL to attach a SQL Server 2008 R2 database with the following code, but I'd like to do this only if the database is not already attached, because trying to attach an already attached database returns an error.

Is there a way using Transact-SQL to test if the database is already attached?

USE [MASTER]
GO
#I'd like to test here if database is already attached
CREATE DATABASE ASPNETDB
    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Data\ASPNETDB.MDF')
FOR ATTACH ;
GO
Community
  • 1
  • 1
Mike Heffelfinger
  • 413
  • 1
  • 8
  • 18

2 Answers2

1
IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME = 'ASPNETDB')
    CREATE DATABASE...
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

Something like this:

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = N'ASPNETDB')
BEGIN
  CREATE DATABASE ASPNETDB
    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Data\ASPNETDB.MDF')
  FOR ATTACH ;
END
Colin Mackay
  • 18,736
  • 7
  • 61
  • 88