3

Can anyone tell me how I would go about checking if a database and tables exists in sql server from a vb.net project? What I want to do is check if a database exists (preferably in an 'If' statement, unless someone has a better way of doing it) and if it does exist I do one thing and if it doesn't exist I create the database with the tables and columns. Any help on this matter would be greatly appreciated.

Edit:

The application has a connection to a server. When the application runs on a PC I want it to check that a database exists, if one exists then it goes and does what it's supposed to do, but if a database does NOT exist then it creates the database first and then goes on to do what it's supposed to do. So basically I want it to create the database the first time it runs on a PC and then go about it's business, then each time it runs on the PC after that I want it to see that the database exists and then go about it's business. The reason I want it like this is because this application will be on more than one PC, I only want the database and tables created once, (the first time it runs on a PC) and then when it runs on a different PC, it sees that the database already exists and then run the application using the existing database created on the other PC.

Improve this question
5
  • 1
    I assume your app must already have a connection to the server? This to me sounds like a deployment instead of something you should embed in an application. You can check sys.databases Commented Aug 6, 2014 at 14:24
  • I don't think the connection to the server is the issue. I'm assuming the application could create a database & tables on the fly, if they don't already exist. Commented Aug 6, 2014 at 14:28
  • This shows you how to check: kellyschronicles.wordpress.com/2009/02/16/…, I'd post it as an answer but it'd just be a copy n paste job. Commented Aug 6, 2014 at 14:30
  • @Tanner you're link worked great, thanks for your help. Problem solved. Commented Aug 7, 2014 at 13:28
  • @Coder92 no problem, I'll add it as a referenced answer shortly Commented Aug 7, 2014 at 13:28

6 Answers 6

Reset to default
6

You can query SQL Server to check for the existence of objects.

To check for database existence you can use this query:

SELECT * FROM master.dbo.sysdatabases WHERE name = 'YourDatabase'

To check for table existence you can use this query against your target database:

SELECT * FROM sys.tables WHERE name = 'YourTable' AND type = 'U'

This below link shows you how to check for database existence is SQL Server using VB.NET code:

Check if SQL Database Exists on a Server with vb.net

Referenced code from above link:

Public Shared Function CheckDatabaseExists(ByVal server As String, _
                                           ByVal database As String) As Boolean
    Dim connString As String = ("Data Source=" _
                + (server + ";Initial Catalog=master;Integrated Security=True;"))

    Dim cmdText As String = _
       ("select * from master.dbo.sysdatabases where name=\’" + (database + "\’"))

    Dim bRet As Boolean = false

    Using sqlConnection As SqlConnection = New SqlConnection(connString)
        sqlConnection.Open
        Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
            Using reader As SqlDataReader = sqlCmd.ExecuteReader
                bRet = reader.HasRows
            End Using
        End Using
    End Using

    Return bRet

End Function

You could perform the check in another way, so it's done in a single call by using an EXISTS check for both the database and a table:

IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = 'YourDatabase')
BEGIN
    -- Database creation SQL goes here and is only called if it doesn't exist
END

-- You know at this point the database exists, so check if table exists

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'YourTable' AND type = 'U')
BEGIN
    -- Table creation SQL goes here and is only called if it doesn't exist
END

By calling the above code once with parameters for database and table name, you will know that both exist.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Connect to the master database and select

SELECT 1 FROM master..sysdatabases WHERE name = 'yourDB'

and then on the database

SELECT 1 FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'yourTable'

i dont know the exact vb syntax but you only have to check the recordcount on the result

Improve this answer

10 Comments

This will work but creates problem on the code side where you need to check for NULL (Nothing) if the database or table don't exist. Better use IF EXISTS(.....) SELECT 1 ELSE SELECT 0 so a return is always guarateed.
Oh the irony of @deterministicFail being corrected on code that didn't deterministically fail...
@Steve in my last sentence i assume to check the count on the resultset. So the if would be in vb and not in T-SQL
Don't forget to check the schema too. AND TABLE_SCHEMA = 'yourSchema'
The point is that you could obtain the result 1 or 0 without getting a DataSet back. Just an ExecuteScalar it is more performant. But, in this context, it is not really important. Just to let you know.
|
3

For tables and other objects inside a database, I usually do it this way but it's really personal preference.

IF OBJECT_ID('dbo.blah') IS NOT NULL
BEGIN

END

For VB.NET, I'd wrap this in a stored procedure and call that. I'm sure there are also ways to do this with Linq.

Improve this answer

Comments

2

You can use This query for check database

IF DB_Id('YourDatabaseName') IS NOT NULL

BEGIN

PRINT 'DB EXISTS'

END

ELSE

BEGIN

PRINT 'DB  NOT EXISTS'

END

Improve this answer

Comments

1 
Friend Function CheckDatabaseExists(server As String, database As String) As Boolean
    Dim connString As String = "Data Source=" + server + ";Initial Catalog=master;Integrated Security=SSPI"

    Dim cmdText As String = "select * from master.dbo.sysdatabases where name='" + database + "'"

    Dim bRet As Boolean = False

    Using sqlConnection As SqlConnection = New SqlConnection(connString)
        sqlConnection.Open
        Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
            Using reader As SqlDataReader = sqlCmd.ExecuteReader
                bRet = reader.HasRows
            End Using
        End Using
    End Using

    Return bRet

End Function
Improve this answer

Comments

0 
   Public Function SQLDatabaseExist(ByVal DefaultConnectionString As String, ByVal DataBaseName As String) As Boolean
    Try
        'CREATE DATABASE
        Dim SqlString As String = ""
        SqlString = "SELECT CASE WHEN EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" & DataBaseName & "') THEN CAST (1 AS BIT) ELSE CAST (0 AS BIT) END"
        Dim ExcRet As Integer = 0
        Using connection As New SqlConnection(DefaultConnectionString)
            Dim command As New SqlCommand(SqlString, connection)
            command.Connection.Open()
            ExcRet = command.ExecuteScalar()
            command.Connection.Close()
            command.Dispose()
        End Using
        Return ExcRet
    Catch ex As Exception
        Return False
    End Try
End Function
Improve this answer

1 Comment

'Sample Default Connection String Dim DefaultConnectionString As String = "Data Source=localhost\SQLSERVER2008;Initial Catalog=Master; User ID=SA; Password='123123'; MultipleActiveResultSets=false; Connect Timeout=15;Encrypt=False;Packet Size=4096;" 'Notice the initial catalog in the connection string must be master!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.